Excel Worksheet Codenames

[Alasdair Stirling]

I am trying to add a new worksheet and then retrieve its
codename with the following code:

Sub alpha()
ThisWorkbook.Sheets.Add
sNewShtName = ActiveSheet.Name
MsgBox (Sheets(sNewShtName).CodeName)
End Sub

If the Visual Basic Editor is open, I can retrieve the
codename without any problem. However, if the Visual
Basic Editor is closed then Excel cannot seem to retrieve
the worksheet codename. Moreover it remains unable to
retrieve the codename until I open the Visual Basic Editor
(even if I save and or close down the workbook and shut
down the application. Finally, it makes no difference
whether I add the worksheet programmatically or manually

Does anybody know of a way to retrieve the codename of a
newly added worksheet without opening the Visual Basic
Editor?

Thanks

Alasdair Stiring

 

[Rob van Gelder]

That works fine for me whether vb is open or closed (I'm running XL2003)...

I've seen another way of doing it:

Workbooks("Name.xls").VBProject.VBComponents("SheetName").Properties("_CodeName").Value

You may need to add a reference to Visual Basic Extensibility

 

[Peter T]

Alasdair's problem has bugged me in XL97 and XL2000. Interesting it does not
appear to occur in later versions.

It seems if new sheets are added, manually or by code when the VBE is
closed, their codenames are not updated.

I'm not sure if this is useful:
Workbooks("Name.xls").VBProject.VBComponents("SheetName").Properties("_CodeN
ame").Value

Because I think "SheetName" is expected to refer to a codename. In other
words it returns what you already know. If "SheetName" is not same as any of
the existing codenames it errors.

Chip Pearson suggested here:
http://tinyurl.com/3me9q
to recompile the project like this
Application.VBE.CommandBars.ActiveMenuBar.FindControl(ID:=578).Execute

But unfortunately this does not work for me even with a reference to Visual
Basic Existensibility.

Dim ob As Object
Set ob = Application.VBE.CommandBars.ActiveMenuBar.FindControl(Id:=578)
MsgBox ob Is Nothing 'returns True for me

Regards,
Peter

 

[Tom Ogilvy]

Sub alpha()
Dim cName As String
Dim vcomp As Object
Dim sNewShtName as String
ThisWorkbook.Sheets.Add
sNewShtName = ActiveSheet.Name
For Each vcomp In ThisWorkbook.VBProject.VBComponents
If LCase(vcomp.Properties("Name").Value) = _
LCase(sNewShtName) Then
cName = vcomp.Properties("_CodeName").Value
Exit For
End If
Next
if cName = "" then
msgbox "Not found"
Else
MsgBox cName
End if
End Sub

 

[Peter T]

Tom - this looks great and I think solves my problem.

I say "think" because once after a lot of insert / delete / renaming of
sheets & codenames I got Msgbox "Not found" (VBE closed).
But I cannot recreate what I did! If I do I'll post back.

Thanks,
Peter

PS For archive searchers I see the OP posted a follow up today in a new
thread with
subject: "Excel Worksheet Codenames 2"

 

[Peter T]

Had another look and might have stumbled on something a bit simpler. I
found that running an empty loop through .VBProject.VBComponents seemed to
fix, and so came up with this:

Sub alpha2()
Dim cName As String
Dim sNewShtName As String
Dim sTitle As String
Dim oVBProj As Object
'try with VBE open & closed
ThisWorkbook.Sheets.Add
sNewShtName = ActiveSheet.Name
cName = ActiveSheet.CodeName
If cName = "" Then
Set oVBProj = ThisWorkbook.VBProject
Set oVBProj = Nothing
cName = ActiveSheet.CodeName
sTitle = IIf(cName = "", "!!", "Corrected")
Else: sTitle = "OK" 'with VBE open
End If

MsgBox sNewShtName & vbCr & cName, , sTitle

End Sub

Maybe this is another way of compiling the project as suggested by Chip
Pearson in the link I mentioned earlier.

Regards,
Peter

 

[Rob van Gelder]

You're right - I wasn't concentrating.