Worksheet.CodeName empty

I am trying to reference newly added Worksheet by it's CodeName property. The problem is that CodeName returns empty string unless run from debugger.

Set tableSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
MsgBox tableSheet.CodeName

Even this simple example d开发者_开发问答oesn't work unless I put a break point on MsgBox line. What is the problem with this?

I was able to duplicate your issue. Some googling revealed this answer:

Sub test()
Dim tablesheet As Excel.Worksheet

Set tablesheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
MsgBox ThisWorkbook.VBProject.VBComponents(tablesheet.Name).Properties("Codename")
End Sub

I think you have to check Microsoft Visual Basic for Applications Extensibility 5.3 in Tools>References.

I also needed to read codename for new sheet. This solution worked for me:

1. Go to Trust Center, under Macro settings check "Trust access to VBA project model".
2. Now just put this three lines before the line where you need code name. It won't work without this. It is a VBA quirk.

On Error Resume Next
Debug.Print ActiveWorkbook.VBProject.VBComponents(Worksheets(ActiveSheet.Name).CodeName).Properties("Codename")
On Error GoTo 0

3. Now use your code name like this:

strActiveSheetCodeName = ActiveWorkbook.VBProject.VBComponents(Worksheets(ActiveSheet.Name).CodeName).Properties("Codename")

I can confirm this behavior. I have never used CodeName before, I use sometimes Name to reference a sheet.

Sub Test()
    Dim tableSheet As New Worksheet

    Set tableSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    MsgBox tableSheet.Name
End Sub

This gives the name of the sheet in the MsgBox and it is not only readable, you can change the name of the sheet if you want.

I have a similar problem for a new sheet that created by macro (it would have a blank codename unless you open the Macro Editor). For my case, since I need the code name to insert some macro to the new sheet. So I use the following code, and it works. It seems the codeName would have value, due to my code access Name attribute of 'VBComponents.item', which is codeName attribute for sheet.

Note: I am not sure why, below code would open the VBA Editor automatically.

Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent

Set VBProj = ActiveWorkbook.VBProject

Dim i
For i = 1 To VBProj.VBComponents.Count
    If VBProj.VBComponents.Item(i).Name = ActiveSheet.CodeName Then
        Set VBComp = VBProj.VBComponents.Item(i)
    End If
Next