Strange probem with capitalization of object

[kevlarmcc]

I have two copies of code that I swear are indentical except for the
captialization of an object, which seems to be automatic. Somehow one copy of
the code doesn't auto cap the object and it is that copy that works. Code is
below:

Sub RunAll()

For Each Worksheet In Worksheets
Select Case Worksheet.Name
Case "Report 1"
Case Else
Worksheet.Activate
If ActiveSheet.Name = "BGE" Then _
ActiveSheet.Name = "Sheet999"
End Select
Next Worksheet
End Sub

When Name is capped, it doesn't work. With lowercase name it works. I can't
seem to replicate the non-capped object because when writing new code it
won't let me not capitalize it. I am not sure what I did to get the object
not capitalized in the first place. Anyone understand this?

 

[Dave Peterson]

First, using a variable named Worksheet is a bad idea. Excel has an object that
uses that name (it's a worksheet <vbg>.)

Instead

Option Explicit
Sub RunAll()
dim wks as worksheet
for each wks in worksheets
case "Report 1"
......

 

[Mike H]

Hi,

Text comparisons using = in VB are case sensitive. If you want to ignore
case try this simpler version of your code

Sub Rename_Shts()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If UCase(ws.Name) = "BGE" Then
ws.Name = "Sheet999"
End If
Next
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[OssieMac]

If I understand your problem correctly then it is a case sensitive problem in
the comparison. Code it like the following where it temporarily converts both
to lower case for comparison.

Select Case LCase(Worksheet.Name)
Case LCase("Report 1")
MsgBox "Case Report 1"

alternatively convert both to uppercase.

Select Case UCase(Worksheet.Name)
Case UCase("Report 1")
MsgBox "Case Report 1"

 

[kevlarmcc]

I am not sure I am explaining it correctly. It's not the value for Name it is
the actual object Name.

Here is the code that works:

Sub RunAll()
For Each Worksheet In Worksheets
Select Case Worksheet.name 'This just skips the first sheet and isn't
crucial
Case "Report1"
Case Else

'Here is the issue; note name instead of Name
Worksheet.Activate
If ActiveSheet.name = "BGE" _
Then ActiveSheet.name = "Sheet999"
End Select
Next Worksheet
End Sub

Forgive me if the misunderstanding is mine; I am new to Excel code!

 

[kevlarmcc]

I thought I was choosing to use the object itself rather than a variable for
it. I get the same results with using a variable such as "ws". Thanks for the
help, as I am new to this. What's second?

 

[Rick Rothstein]

I **think** that you have declared "name" (with the small "n") as a variable
somewhere where the current module can see it. I can duplicate your problem
easily enough as follows...

Dim name As String
Sub RunAll()
For Each Worksheet In Worksheets
Select Case Worksheet.name
Case "Report1"
Case Else
Worksheet.Activate
If ActiveSheet.name = "BGE" _
Then ActiveSheet.name = "Sheet999"
End If
End Select
Next Worksheet
End Sub

The "n" in the property name for your Worksheet variable in the Select Case
statement will always be lower case matching the case used in the Dim
statement. As Dave pointed out in his response, it is a bad idea to use data
type names, property names, or built-in function names as names for your own
variables.

 

[Mike H]

Hi,

You confused me by referring to 'name' as an Object, it isn't. Activesheet
is the Object and Name is a property of that object.

But, I'm still no closer to understanding the issue. If the syntax you have
used is correct; and it is in the sample code you posted, then 'name' will
auto capitalise to Name.

The code you posted in you first post and the one in the second both work
for me.


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[kevlarmcc]

Sorry for the confusion. I am still getting the terminology down. And the
code works for me when I create a new workbook and test it, just not in the
book I want it too, so I've narrowed it down to some difference in the
workbook I am trying to run it in. But still no idea what the difference
might be.

 

[Dave Peterson]

Second was the stuff after "instead".

I'd look for a variable named "Name" or a module named "Name" or a
procedure/function named "Name".

That's another string of letters that you shouldn't use for any of those things.

 

[kevlarmcc]

Thanks Rick. I found one problem. The sheet named BGE actually has a space at
the end of it, so the comparison wasn't being recognized. When I add the
space in the quotes "BGE " rather than "BGE" it works. I am sorry to have
wasted everyone's time, but I have learned from everyones' help.