From: kevlarmcc on
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?

"Dave Peterson" wrote:

> 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"
> ......
>
>
>
> kevlarmcc wrote:
> >
> > 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
> .
>
From: Rick Rothstein on
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.

--
Rick (MVP - Excel)



"kevlarmcc" <kevlarmcc(a)discussions.microsoft.com> wrote in message
news:B8E2ADCD-38A8-4A5B-BA80-6ACB97D5EDE5(a)microsoft.com...
> 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!
>
>
> "Mike H" wrote:
>
>> 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.
>>
>>
>> "kevlarmcc" wrote:
>>
>> > 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?

From: Mike H on
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" wrote:

> 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!
>
>
> "Mike H" wrote:
>
> > 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.
> >
> >
> > "kevlarmcc" wrote:
> >
> > > 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?
From: kevlarmcc on
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.

"Mike H" wrote:

> 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" wrote:
>
> > 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!
> >
> >
> > "Mike H" wrote:
> >
> > > 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.
> > >
> > >
> > > "kevlarmcc" wrote:
> > >
> > > > 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?
From: Dave Peterson on
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 wrote:
>
> 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?
>
> "Dave Peterson" wrote:
>
> > 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"
> > ......
> >
> >
> >
> > kevlarmcc wrote:
> > >
> > > 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
> > .
> >

--

Dave Peterson