Actually Renaming an Excel Worksheet. 3

[grnzbra]

I have an Excel workbook with several worksheets. These name show up in the VBAProject/Microsoft Excel Objects as: SheetName(Label on Tab).

The VBA wants to work with the sheet name not the label. ie PMSchedule, not PM Schedule. I needed to add a new sheet and ended up with Sheet1(Equipment-Warranty).
The VBA will not work with Equipment-Warranty but will work with Sheet1. How can I change Sheet1 to EquipmentWarranty?

 

[SkipVought]

Hi,

The VBA wants to work with the sheet name not the label. ie PMSchedule, not PM Schedule.

Not so!

The following are equivalent VBA statements:

PMSchedule.Activate
Worksheets("PM Schedule").Activate

To change...

Sheet1.CodeName = "EquipmentWarranty"
EquipmentWarranty.Activate

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

If it's only a design requirement, you can change code name manually. When you have sheet in project explorer selected (as in your image), change (Name) property in properties window.
Note that code name is a part of vba project, when sheets are added dynamically, project structure is not automatically updated.

combo

 

[grnzbra]

Thanks to both of you. Combo, you pointed me in the right direction. After spending an unacceptable amount of time looking for it,I had assumed there was no properties window so I had given up looking for one. Since you said that there was a properties window, I continued my search and eventually found it.
Skip, thank you for pointing out that I could use the tab label. I had used that because the original developer had used that Worksheets("") notation but he used it on the Equipment sheet so I didn't realize the difference. This clarifies a lot of things.
Thank you both.

 

[combo]

Skip,
CodeName property is read-only. Code name can be changed programmatically by hiddden r/w [_CodeName] property.

combo