renaming worksheets/objects in VBA

[Guest]

Hello,

I am using Excel 2003, and I have recently inherited a workbook with 5
worksheets. I did not create the workbook or name the sheets originally.
When I open the VBA editor, my worksheets are labelled like this in the
Project Window under Microsoft Excel Objects:

Sheet1 (Greeting)
Sheet10 (Work Data)
Sheet43 (Misc Calcs)
Sheet65 (Offerings)
Sheet8 (Data Entry)

When I view the workbook in Excel, the sheet tabs are named only with the
above text that is in the ( )s, and NOT with the sheet number. So in Excel
the tabs running across the bottom are labelled:

Greeting, Work Data, Misc Calcs, Offerings, Data Entry

I know how to change these names in ( )s both within Excel and with VBA.
But what I do not know is how to change or remove the "Sheet65" portion of
the name that displays in the VBA Editor.

If I cannot remove the "Sheet#" portion, can I at least change them to be
chronoligal?

As a minimum, when I open the VBA editor, I would like my worksheets
labelled like this in the Project Window under Microsoft Excel Objects:

Sheet1 (Greeting)
Sheet2 (Work Data)
Sheet3 (Misc Calcs)
Sheet4 (Offerings)
Sheet5 (Data Entry)

Any ideas?

Thanks,

Scott

 

[Guest]

the name you refer to is the "codename". it can't be changed
programmatically, but you can change it by clicking view/properties while in
VBA and changing the name in the properties window. Or, while in Excel,
activate the control toolbox (view/toolbars/control toolbox) and click the
properties button on the toolbar.

the codename can be used to reference worksheets instead of the tab name.
since the code name generally changes less frequently than the tab name, it
requires you to change your code less often (and it allows users to change
the tab names to whatever they want w/o causing problems).

Assuming a worksheet has a tab name of "Sheet1" and its codename is
Sheet1CodeName, you could reference it using either name:

Worksheets("Sheet1").Range("A1").Value = 1
Sheet1CodeName.Range("A1").Value = 1

 

[Guest]

One other caveat - if the workbook contains VBA code, make sure the original
creator did not use the codenames in the program code. If so, changing them
will require you to modify the code.