I struggled a lot with this issue as the codename returned by VBA wasn't correct! A new sheet wasn't matching its codename in my project, ex.: a sheet named Sheet13 had a codename like Sheet21, and declaring the VBProject didn't make it for me. Worksheets("Sheet13").codename didn't even return "Sheet21", but the codename of another sheet. Quite confusing.

So in the end, I got the code name with this:

dim VBComp As VBIDE.VBComponent
dim SheetName as string, SheetCodeName as string

SheetName = Activesheet.name

For Each VBComp In ThisWorkbook.VBProject.VBComponents
If VBComp.Type = vbext_ct_Document Then
If SheetName = VBComp.Properties.Item(7) Then
SheetCodeName = VBComp.Name
Exit For
End If
End If
Next

Thought I would share, although you may all be retired by now!

When you add sheets is the best time to edit its codename...

With ActiveWorkbook.Sheets.Add
.Name = "SheetName": .CodeName = "SheetCodeName"
End With

...or set an object variable to each sheet and ref by sheetname...

Dim wks As Worksheet
Set wks = Sheets("SheetName")
wks.CodeName = "SheetCodeName"

...if you're looping sheets in a workbook.

If you're assigning codenames based on existing sheetnames then you
might be better to store a set of delimited strings and work through
the set in order. (This requires the strings be sync'd)

Const sNames$ = "Sheet1,Sheet2,Sheet3,Sheet4"
Const sCodeNames$ =
"Sheet1Codename,Sheet2Codename,Sheet3Codename,Shee t4Codename"

Dim i%, vaNames, vaCodeNames

vaNames = Split(sNames, ","): vaCodeNames = Split(sCodeNames, ",")
For i = LBound(vaNames) To UBound(vaNames)
ActiveWorkbook.Sheets(vaNames(i)).Codename = vaCodeNames(i)
Next

Also, you might want to note that you can expect sheetnames to be
changed whereas codenames are likely to persist. In this case you can
use the codename to find a sheetname...

Dim wksSource As Worksheet
Set wksSource = Sheets(Get_SheetTabName(ActiveWorkbook,
"SheetCodeName")

Function Get_SheetTabName(Wkb As Workbook, CodeName As String) As
String
Dim Wks As Worksheet
For Each Wks In Wkb.Worksheets
If Wks.CodeName = CodeName Then _
Get_SheetTabName = Wks.name: Exit Function
Next
End Function

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion