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

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

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("Shee tName").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 he
http://tinyurl.com/3me9q
to recompile the project like this
Application.VBE.CommandBars.ActiveMenuBar.FindCont rol(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.FindCont rol(Id:=578)
MsgBox ob Is Nothing 'returns True for me

Regards,
Peter

"Rob van Gelder" wrote in message
...
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("_CodeN
ame").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

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

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

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






"Peter T" <peter_t@discussions wrote in message
...
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

You're right - I wasn't concentrating.

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


"Peter T" <peter_t@discussions wrote in message
...
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("Shee tName").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 he
http://tinyurl.com/3me9q
to recompile the project like this
Application.VBE.CommandBars.ActiveMenuBar.FindCont rol(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.FindCont rol(Id:=578)
MsgBox ob Is Nothing 'returns True for me

Regards,
Peter

"Rob van Gelder" wrote in message
...
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("_CodeN
ame").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