Change Worksheet Codename

[Lucasaran]

I have a command button in a userform that runs this macro:

Dim NewLabelName As String


'Create and name new sheet (including codename)
   ActiveWorkbook.Sheets.Add  'creates new worksheet
   ActiveSheet.Name = Product_Name_La.Caption  'Renames the Worksheet
   ActiveSheet.Range("B2") = Product_Name_La.Caption 'Places the Worksheet name into a cell on the Worksheet
   NewLabelName = ActiveSheet.Range("B2")  'Defines the variable "NewLabelName" (for later use)
   ThisWorkbook.VBProject.VBComponents(Sheets(NewLabelName).CodeName).Name = "ProductLabel" _
   & BD.Range("B2").Value 'Renames the Worksheet codename **this line is where the error occurs**
   'BD.Range("B2").Value represents the amount of current worksheets plus 1

(A whole bunch of other code follows)

It worked fine at first, but after I close and reopen the Workbook excel throws "run-time error '9': Subscript out of range." If I click "Debug" then open the "Debug" dropdown in VBA and click the "Step into" command, excel throws another error: "Can't enter break mode at this time." If I click "Continue", excel runs all of the code correctly (including changing the codename) and the problem does not occur again until The Workbook is closed and reopened.

The error can also be corrected by making any change to the line of code where the error occurs (for instance, change "Sheets(NewLabelName)" to "ActiveSheet"). But the error consistently reoccurs the first time the code is called after opening the Workbook.

I really want the codename to be changed for each new Worksheet, but I can't have this error occuring every time my boss opens the Workbook. How can I solve this problem?

 

[mikey38654]

Sorry to resurrect an old post; however, I think I may have a couple of suggestions to add. I am not very new to VBA, but still a little "green". Ultimately, I think I have some valid ideas.

First, I wonder if you couldn't build an array when the worksheet opens that gathers the worksheets and their code names, and assign each to a variable? Especially if you have something in the worksheet to verify the content matches the content you intend when you address a given code name? However, I see this would be tedious and not an optimal approach.

Second, I also wonder if you could use VBA code to disable the deletion and renaming of worksheets in the GUI, thereby making it so that your code always works with that worksheet? I can see the user messing up the sheet and copying it, then your code doesn't work on the new sheet, but maybe that's something that could be anticipated and programmed too? (For example, capture whether user copies a given sheet into the same workbook, and make the code adjust itself to work with it.)

I do tend to overthink and do show quite a bit of optimism here, so maybe code to achieve these thinks would be bulky and cumbersome, but perhaps the food-for-thought can help others in these types of endeavors. I, too, am disappointed that Microsoft doesn't seem to provide us with a workable resolution for this (that I know of, anyway).

 

[Jaafar Tribak]

Just came accross this thread and would like to suggest using the hidden Property: _CodeName to change the sheet codename without the need to use the vba project object model which relies on the user macro security settings.

So intead of this :

 ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "BlaBlaBla"

Use This :

Sheet1.[_CodeName] = "BlaBlaBla"

 

[mikerickson]

Very nice. Thank you.

 

[Jaafar Tribak]

Very nice. Thank you.

You are welcome mike.

I have found this rather weird and perplexing oddity while doing some further testings in order to change the sheet code name out of the sheet User Interface name. .

This line throws an error (runtime error #424 - Object Required)

Worksheets("Sheet1").[_CodeName] = "BlaBlaBla"

This line too, throws the same error:

Sheets.Item("Sheet1").[_CodeName] = "BlaBlaBla"

This, however, works as expected:

Dim ws As Worksheet

Set ws = Sheets("Sheet1")
ws.[_CodeName] = "BlaBlaBla"

In other words, simply storing the sheet object in a typed variable and calling the _CodeName Propery out of the variable makes a difference ... This doesn't make any sense to me since the variable ws points to Sheets("Sheet1") !!!

 

[mikerickson]

When I look in the Object Browser, the Item property of a Sheets object is typed Object, not Sheet, which might be a reason for that oddity.

 

[Jaafar Tribak]

When I look in the Object Browser, the Item property of a Sheets object is typed Object, not Sheet, which might be a reason for that oddity.

Even if I don't use the Item Property, I still get an error when executing this line:
Sheets("Sheet1").[_CodeName] = "BlaBlaBla"

 

[mikerickson]

The Sheets object of a Worksheet object does not show any arguments in the Object Browser.
I think that Sheets("Sheet1").[_CodeName] = "BlaBlaBla" uses the default property of a Sheets object, the Item property.
(Just as Cells(1,2).Value = "x" uses the default Item property of the Range object (Cells does not take arguments)