When you add a new sheet with the VBE closed its codename does not exist,
you'll return an empty string (the codemodule is not even created). It's
been like that since Excel-97.

To work around and create the name (ie the sheet's object module) you can
programmatically open/close the VBE. Or, if 'trust access to VB Project' is
allowed, simply refer to the project, eg

Set vbp = activeworkbook.vbproject
or
s = activeworkbook.vbproject.name

Regards,
Peter T


"Reinhard Thomann" wrote in message
...
My application uses .CodeName property to identify XL sheets.
When copying a sheet within a workbook the new sheets gets the same
CodeName
plus Index.
This fails in XL2007 still SP2.

Steps to reproduce bug:

1. Create new workbook
2. Modify .CodeName property of sheet 1 (e.g. test) in VBE
3. Save workbook
4. Close workbook and open it again NOTE: don't start VBE (F11) because
otherwise it works
5. Copy sheet 1 immediately after opening
6. Check .CodeName property of copied sheet
7. BUG: CodeName is not e.g. test1

Does anybody knows a workaround to this problem?

TIA
Reinhard