When do worksheets get assigned their .CodeName property?

Question

So, in the process of my VBA here, I have a sub in my workbook that creates a sheet, and dynamically adds code to that new sheets activate event. It's pretty cool actually. However, I'm running into a problem with the sheets code name, where the new sheet is not assigned a code name when I go to reference it.

The code used to get a reference to the new sheet code module is: With ActiveWorkbook.VBProject.VBComponents(CName).CodeModule where CName is the sheets code name.

I get the CName with: CName = Workbooks(ExcelFormName).Sheets(NewSheetName).CodeName Where NewSheetName is a global constant. This always works because this code is executed in the same function as the creation of the sheet, thus the user has not had a chance to rename the sheet.

I found through testing (including hair pulling) that when i created the worksheet: Set ws = Sheets.Add(, After:=Sheets(Worksheets.count)) the sheet was not assigned a code name unless the vbe window was open.. i used debug.print "Test " & ws.codename to confirm that codename was blank - "" so when I tried to reference the sheet using the code name, i was getting an error. What I found i could do is:

Application.VBE.MainWindow.Visible = True
Application.VBE.MainWindow.WindowState = vbext_ws_Minimize

before I create the sheet, to quickly open and minimize the VBE window (I later close it) which i didn't like but it worked. But now I'm running into issues where some users have security settings that dont allow the vbe window to open.

I noticed, just now, If i loop through all of the vbcomponents in my workbook, without even doing any processing like:

Dim test As VBComponent
For Each test In ActiveWorkbook.VBProject.VBComponents
Next

then after that, the worksheet has a code name.. So i'm wondering, what can I do besides loop through the components like that to make sure the new sheet has a code name, and without doing that, when does a worksheet normally get assigned its code name if the VBE window is not open?

Edit

A picture of the results of your suggestion siddharth:

You can see, I added your suggestion, I got the error 9, subscript out of range error

Edit2

Dim CName As Variant
CName = Workbooks(ExcelFormName).Sheets(NewSheetName).CodeName
With ActiveWorkbook.VBProject.VBComponents(CName).CodeModule 
    StartLine = .CreateEventProc("Activate", "worksheet") + 1
    .InsertLines StartLine, "Dim numRows As Long"
    .InsertLines StartLine + 1, "Dim numColumns as long"
    .InsertLines StartLine + 2, "numRows = 0"
    .InsertLines StartLine + 3, "numColumns = 0"

    'mode codez

I'm dynamically adding code to the new sheets activate event. I would add it normally but this sheet is deleted when the user closes the book. This particular sheet is not needed all the time, so I don't want it there unless the particular user needs it :)

Answer 1

Yes this is a very old problem. To access the codename, use this

Dim ws As Worksheet

Set ws = Sheets.Add(, After:=Sheets(Worksheets.count))

Msgbox ThisWorkbook.VBProject.VBComponents(ws.Name).Properties("Codename")

However for this code to work, you have to check Trust access to the VBA-Project Object model