Changing code name of worksheet

[Night_Rain]

Using VBA, how can I change the code name of a worksheet at runtime, as opposed to the display name of the worksheet (Worksheet(x).Name = "blahblahblah")?

 

[Damon Ostrander]

Hi Night_Rain,

The codename of a worksheet cannot be changed programmatically at run time. There is a good reason for this--it could make other code that has already been scanned by the VBA engine for correct object referencing become invalid at run time.

Damon

 

[Tom Urtis]

Using VBA, how can I change the code name of a worksheet at runtime...

Well, let's say you have a workbook with 10 sheets and one of them has the codename Sheet5. This macro will change that sheet's codename to Sheet005:

Sub ChangeCodeName()
ThisWorkbook.VBProject.VBComponents(Sheet5.CodeName).Properties("_CodeName") = "Sheet005"
End Sub


Use this with caution, as subsequent code in your project will fail if it depends on a codename property of Sheet5 for that worksheet object.

 

[Damon Ostrander]

programmatically changing worksheet codename

Thanks Tom,

I didn't think that was possible based on what it says in the help files. I learned something.

Damon

 

[Tom Urtis]

Hi Damon - -

Thanks for the note. I just now looked at Help for the CodeName property and I see what you mean. I gave up on Help a few years ago, after coming across too many factual errors. Help is definitely an area where Microsoft can improve its Excel product in my opinion.

 

[Night_Rain]

Thank you both.

 

[ptx]

Gotta Say, great answers here.
However i wanted to ask sth. For Future People

Why is Tom Urtis using .Properties("_CodeName") when the VbComponents itemList already contains the CodeName with the name .name?
Am i overlooking any edgecase / usecase?

ThisWorkbook.VbProject.VBComponents(worksheet.CodeName).Name = "CodeNameHere"