'Name' of object '_VBComponent' failed

[vimmi]

Trying to use sheet code names instead of sheet names, in case users change the sheet name, and the below returns the error "'Name' of object '_VBComponent' failed".

    Test2.Delete
    Worksheets.Add
    ws_name = ActiveSheet.Name
    ThisWorkbook.VBProject.VBComponents(Sheets(ws_name).CodeName).Name = "Test2"
    Sheets(ws_name).Name = "Test2"

The above works if

 Test2.Delete

is replaced with

 Sheets("Test2").Delete

then it works fine but that defeats my goal of using code names.

 

[steve the fish]

Hi. I think id prefer the error and then tell them to rename the sheet as was. You cant do what you are attempting there i believe because excel will think Test2 sheet exists when you try to recreate it because it did at runtime.

 

[vimmi]

Thanks Steve! I get what you mean, fair enough if that's the case it's not the end of the world