VBA -- setting codename gives subscript error

[billbrunt]

Hi -

I've Subgot a small snippet of code as follows:

Sub subSetWorksheetCodeName()
    Dim lsStrSheetName As String
    Dim lsWkb As Workbook
    Dim lsWks As Worksheet
    
    Workbooks.Open "C:\Users\bill.brunt\Documents\ExcelTest\LPConfigurationTest.xlsx"
    
    Set lsWkb = Workbooks("LPConfigurationTest.xlsx")
    lsWkb.Sheets.Add After:=lsWkb.Sheets(lsWkb.Sheets.Count)
'
'   Rename Sheet
'
    lsStrSheetName = "Versions"
    ActiveSheet.Name = lsStrSheetName
    Set lsWks = ActiveSheet
'
'              ' Change Worksheet CodeName
'
'
'   Bill Brunt -- 07/07/11 Taken from [URL]http://www.exceltip.com/st/Change_the_worksheet_codemodule_names_using_VBA_in_Microsoft_Excel/406.html[/URL]
    On Error Resume Next
    lsWks.Parent.VBProject.VBComponents(lsWks.CodeName).Properties("_CodeName") = "wsVersions"
    Debug.Print "Err.Number = " & Err.Number & " Err.Description = " & Err.Description

End

If I call this from Sub_WorkbookOpen, I get the following:

Err.Number = 9 Err.Description = Subscript out of range

However, if I run this same code from the IDE, it works fine.

Any ideas?

 

[billbrunt]

I found this: https://dutchgemini.wordpress.com/2009/12/

which explains it and works.

I might have had some trouble with this except we consistently use a modified hungarian notation form which prevented another object from having the name of Sheet1, i.e. Sheet name and worksheet codename.