XL2007 .CodeName bug when copying sheet

[Reinhard Thomann]

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

 

[royUK]

Followed your instructions and the new sheet appears in the VBE as
Sheet1(Sheet1(2)).

 

[Peter T]

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]

Hi Peter, royUK

from Excel 97 to 2003 CodeName property workes fine.
Every time a user copies a sheet manually the new one will get CodeName =
CodeNameOfCopiedSheet + Index. Due to this behaviour it's easy for me to
determine
sheets by CodeName property (ActiveSheet.Codename Like Name*).
It fails in Excel 2007 also SP2. Wonder that nobody cares about - is it
possibly a feature? (I can't believe).
I'll give your solution a try.

Thanks
Reinhard

PS. Hope that Microsoft will fix this problem soon!

 

[Peter T]

from Excel 97 to 2003 CodeName property workes fine.

Again, a CodeName will not exist until the sheet's object module is created,
which you can force with one of the ways I suggested previously if/as
necessary.

If the VBE is open, or you are copying a sheet that already has its
codemodule, you do not need to do anything.

It's been like this from 97 to 2007-SP1 (I assume similar in SP2 but I don't
have it to test)

Regards,
Peter T

 

[Reinhard Thomann]

Hi Peter,

thanks for help. The question is, why does Excel 97 to 2003 create the
correct
CodeName and Excel 2007 not. The conditions are the same.
My current (ugly) workaround is to open and close VBE every time a new sheet
is created.
This is only necessary in Excel 2007. The older Excel versions don't need
this workaround.

Your workaround looks smarter. I'll give it a try.
Set vbp = activeworkbook.vbproject

But the best thing would be that Excel 2007 handles CodeName property like
the older versions.

Thanks
Reinhard