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"

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy


"Alasdair Stirling" wrote in message
...
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