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!