Prev: duplicating data
Next: Search Directories/SubDirectories and Display in ListBox or Ms
From: kevlarmcc on 1 Apr 2010 11:30
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 1 Apr 2010 11:48
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: Mike H on 1 Apr 2010 11:51
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: OssieMac on 1 Apr 2010 11:55
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"

--
Regards,

OssieMac


"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 1 Apr 2010 12:20
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?
 |  Next  |  Last
Pages: 1 2 3
Prev: duplicating data
Next: Search Directories/SubDirectories and Display in ListBox or Ms