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):
worksheet.add
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:
Hi Luke,
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.
Damon
Posted by Ivan F Moala on November 17, 2001 2:50 PM
The following worked for me ??
Excel2000
Not sure why it wouldn't for you?
Sub addsh()
Dim a
Worksheets.Add
ActiveSheet.Name = "R1"
a = ActiveSheet.CodeName
'in return I get a = ""
MsgBox a
End Sub
Posted by Luke on November 18, 2001 1:27 AM
which is probably the difference; I work with excel 97 and am thinking maybe it is a glitch with billy's developement team. My msgbox really returns ziltch. I even tried using a new workbook in a freshly opened excel using only the described code (including the messagebox) to make sure that there was nothing in my program that stood in the way. The weird thing is, as described in the earlier question a few days an a hundred questions down (codename - Luke 13:33:38 11/14/01), that when I have the VBA editor on, I get a codename and the problems don't occur, with the editor off, I don't get a codename. That was also why during devellpement, I never caugt the problem, and after discovery it took me some time to figure out the problem. It really is as simple and confusing as that. It is like a lightswitch; now you see it, now you don't. The problems only occur with the light off, but I need the light on to discover the problem. Worked around the problem now, but it kind a cramped my code.
Luke
