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("Shee tName").Properties("_CodeName").Value

You may need to add a reference to Visual Basic Extensibility


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"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