Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange probem with capitalization of object
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange probem with capitalization of object
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange probem with capitalization of object
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange probem with capitalization of object
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange probem with capitalization of object
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange probem with capitalization of object
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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange probem with capitalization of object
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" wrote in message ... 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange probem with capitalization of object
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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange probem with capitalization of object
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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange probem with capitalization of object
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange probem with capitalization of object
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. "Rick Rothstein" wrote: 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" wrote in message ... 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? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Strange Excel Application Object Behavior | Excel Programming | |||
strange error when assigning macro to a form object! | Excel Programming | |||
FormulaR1C1 gets strange application/object defined error | Excel Programming | |||
Gridline printing probem | Excel Discussion (Misc queries) | |||
excel's probem | Excel Programming |