Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2007 .CodeName bug when copying sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2007 .CodeName bug when copying sheet
Followed your instructions and the new sheet appears in the VBE as Sheet1(Sheet1(2)). -- royUK Hope that helps, RoyUK For tips & examples visit my 'web site' (http://www.excel-it.com/) ------------------------------------------------------------------------ royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92804 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2007 .CodeName bug when copying sheet
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2007 .CodeName bug when copying sheet
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2007 .CodeName bug when copying sheet
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2007 .CodeName bug when copying sheet
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 "Peter T" <peter_t@discussions schrieb im Newsbeitrag ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL2007 .CodeName bug when copying sheet | Excel Programming | |||
XL2007: Copying worksheets between workbooks | Excel Programming | |||
change sheet codename | Excel Programming | |||
Selecting a sheet by codename | Excel Programming | |||
Using sheet codename problems | Excel Programming |