MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Luke on November 14, 2001 1:33 PM

I have had a problem come up using codenames.
I have an almost completely VBA run program. Using this I also add sheets and work with them. The odd thing now is, I discovered; that I cannot use the codename to identify the new sheet. Old sheets work just fine, is recognized in the new sheet, but activesheet.codename is not (it is with earlier sheets). The really odd thing is that when I turn on the VBA editor, the problem is gone, turn it off and there is the problem again with jet when I create a new sheet. It was like trying to fine the problem with the ilghts off, but with a lot of catchhooks I found it. I have altered the entire code to use, but I really would have like to use the codename in order to be able to play with the name. Can anybody tell me what the problem may be??? and how to solve it. Some of the things I started using just before I discovered the problem are calculationmanual or -automatic and enableevents true/false. I don't have a previous version anymore since The entire program seemed to work fine (more that a half year already but I never had to create a new sheet like I did now)

Posted by Damon Ostrander on November 14, 2001 4:35 PM

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.