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?

 

[RickXL]

Hi,

I have to say that I am not a big fan of using CodeNames, however ...

Try trapping the errors for that particular line and see if it works then. For instance:

    On Error Resume Next
    ThisWorkbook.VBProject.VBComponents(Sheets(NewLabelName).CodeName).Name = "ProductLabel" & BD.Range("B2").Value
    On Error GoTo 0

It is not tested because you have not provided enough information for me to make a working macro but I did test something similar.

Regards,

 

[mikerickson]

Have you tried qualifying the Sheets?

ThisWorkbook.VBProject.VBComponents([B]ThisWorkbook[/B].Sheets(NewLabelName).CodeName).Name = "ProductLabel" & BD.Range("B2").Value

 

[Sektor]

Why would you change programmatic name? You won't be able to use in run-time.

 

[Jerry Sullivan]

Building on Sektor's response... Some consider it a best practice to have VBA code reference a worksheet's CodeName instead of its (tab) Name because the (tab) Name can more easily be modified by the user. There's some validity to that, however since you are adding sheets at Run-Time, your VBA code (written at Design-Time) will not have been able to use the dynamically added CodeName references.

Another more problematic reason to reconsider your approach, is that it will only work if the users have setup their Macro Settings in the Trust Center to "Trust access to the VBA Project object model".

Most users don't have this option selected, and for that reason, I'd recommend not using any code that attempts modify the VBProject unless it is for your personal use or to be shared with other developers who understand how it works.

I'd suggest you consider alternatives that don't require renaming of sheet's CodeName at Run-Time.

 

[RickXL]

Some consider it a best practice to have VBA code reference a worksheet's CodeName instead of its (tab) Name because the (tab) Name can more easily be modified by the user.

While that is true, it is also possible to get in even more of a mess and one that the user cannot fix by himself or even with help over the telephone.

Problems start when the user deletes a Worksheet then tries to re-create it with the same name. Particularly if the code is protected, it is not easy to get things going again because the new Worksheet will have a different CodeName from the original.

The current situation with the original question also highlights a problem. If the CodeName is used in the VBA code and it changes or is deleted by the macro then the code becomes invalid at that point and it stops working.

Excel does not have a guaranteed method in this regard so personal preferences come into play.

Just my two cents worth ...

 

[Lucasaran]

RickXL's code solved the problem.

I would like to extend my thanks to all for the information that each of you provided. I am still fairly inexperienced at writing VBA code, and I sometimes get a little overzealous when trying to organize my work.

I already had the "Trust access to the <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> Project object model" setting enabled due to a couple of other Workbooks created for my own use; I hadn't considered that changing the Worksheet CodeName might require the user to enable this setting. This fact alone has caused me to reconsider my approach.

Thanks everyone!

 

[RickXL]

Glad to be of assistance.

If you are like me, you will develop your own way of doing things based on what worked in the past.

When it comes to users changing Worksheet Names and then complaining that the code no longer works, I just tell them not to change the name! They soon learn. Others try to be more helpful.

Regards,

 

[Sektor]

Well, using code names help avoid the problem of user changing sheet's name. Referring by position is too. So, using code name is viable solution. But as RickXL noticed, deleting sheet and recreating it won't get the original name back whilst using sheet's name will avoid this issue. Thus, before using code name, you should consider all pros and cons.

 

[RickXL]

Well, using code names help avoid the problem of user changing sheet's name. Referring by position is too.

Using Worksheet Position is not safe either. The user can re-arrange the Worksheets and then all bets are off.

This is one of the reasons I get frustrated with Excel. It provides three ways to do something and not one of them is guaranteed to work in all cases.