Why does this code fail?

[AC]

This works using excel 2003

Sub ChangeVBACodeName()
'will change the active sheets code name to MySheet25
Dim SN As String
SN = ActiveSheet.Name
ThisWorkbook.VBProject.VBComponents(SN).Name = "MySheet25"
End Sub

But this does not, Why

Sub test()
Dim SN As String
Sheets.Add.Move after:=Sheets(1)
ActiveSheet.Name = "Sheet22"
SN = ActiveSheet.Name
ThisWorkbook.VBProject.VBComponents(SN).Name = "MySheet25"
End Sub

 

[SydneyGeek]

Have you trusted access to the VB Project?

Home button > Excel Options > Trust Center > Macro Settings

Denis

 

[AlphaFrog]

In your 1st code, the Sheet name and its' VBComponent name are the same name e.g. "Sheet1". So when you want to change the VBComponent name of Sheet1, this works...
ThisWorkbook.VBProject.VBComponents(Sheet1).Name = "MySheet25"

In your 2nd code (test), you create a new worksheet and rename it "Sheet22". It's VBComponent is not named Sheet22. It's VBComponent name is something like Sheet4 or Sheet5, or however many sheets you have already created (including the deleted ones). So there is no VBComponent named "Sheet22" and this fails...
ThisWorkbook.VBProject.VBComponents(Sheet22).Name = "MySheet25"

Try this instead assuming you don't already have a MySheet25 VBComponent.
SN = ActiveSheet.CodeName
ThisWorkbook.VBProject.VBComponents(SN).Name = "MySheet25"

 

[AC]

AlphaFrog, Thanks works fine now