Change codename of worksheet with vba

Viewed 7832

This code works fine with VBE window open, but raises an error Subscript out of range at this line: wB.VBProject.VBComponents(wS.CodeName).Properties("_CodeName").Value = "wsData" when VBE window is closed. Maybe someone can show me what I'm missing here.

Sub newWorkbook()
    Dim wB As Workbook
    Dim wS As Worksheet
    Dim Proj As Object'<=== added

    Set wB = Workbooks.Add
    Set wS = wB.Worksheets(1)
    wS.Name = "Data"

    Set Proj = wB.VBProject '<== added
    'wB.VBProject.VBComponents(wS.CodeName).Properties("_CodeName").Value = "wsData" '<==Original line
    Proj.VBComponents(wS.CodeName).Properties("_CodeName").Value = "wsData" '<== New

    On Error Resume Next
    Application.DisplayAlerts = False
    wB.SaveAs "C:\dummy.xls", 56

    Application.DisplayAlerts = True
    If Not wB Is Nothing Then wB.Close False
    Set wB = Nothing
End Sub
1 Answers
Related