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" wrote in message
...
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" <peter_t@discussions schrieb im Newsbeitrag
...
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