codenames; reply to Damon Ostrander
Posted by Luke on November 15, 2001 3:56 PM
It is not my intention to change codenames, but to use the codenames as a reference like you discribe, but after I create a new sheet using VBA, when I refer to it by using its codename, or ask for its codename, I get nothing in return; Excel seems to not yet have created the codename.
ie (in short):
activeworksheet.name = "R1"
a = activeworksheet.codename
in return I get a = ""
That is the reason for my question. I have my sheet.name; the one you can programmatically change, but I don't want to have to use that name to refer to in my program; I want to refer to the codename so that I can allow the user to change the visible name. Right now I have altered the entire code to use the 'visible' Name, but this means that when I cannot work with dynamic sheet.Name's.
Posted by Damon Ostrander on November 14, 2001 at 16:35:52:
In Reply to: .codename posted by Luke on November 14, 2001 at 13:33:38:
I have to admit that I don't exactly understand the problem you describe, partially because you mentioned you wanted to "play with" the worksheet name. What does this mean? In VBA you are not supposed to be able to change the codename programmatically, only the Name property. The codename is supposed to only be changeable at "design time." And the only difference between the codename and the Name property is that in VBA you can refer to the object directly using the Codename as if it were an object, whereas with a Name you must refer to the object using its collection. For example, cell B4 on a sheet whose Name is "MyData" would be referred to as Worksheets("Mydata").[B4], while a sheet whose codename is "MyData" could be referred to directly as MyData.[B4]. But if you could change this worksheet's codename in VBA to "LukesData", then your reference to MyData.[B4] would no longer work because this name is hard-coded. Perhaps this is the problem you are having. If you really want to be able to change the name of a sheet in VBA, and then refer to it by that name (without knowing what the name will be at the time you write the code) you MUST use the Name property rather than the codename.
I hope this is helpful.