Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97 modules appear as tabs
I'm attempting to re-program a workbook which I originally
designed in Excel 2003, to run in Excel 97 also. I've discovered a few annoying items, such as worksheet-changes not being triggered when data validation drop boxes are used. However, this one has me bamboozled: Modules randomly appear as tabs in the worksheet. When this happens, the whole thing locks up and must be crashed out-of. This only happens with stand-alone modules, not with code that is associated with each sheet. Does anyone have any idea how to resolve this issue? I need these modules to be available to all the sheets at all times. Thanks, in advance, Kate |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97 modules appear as tabs
I've never seen this problem.
But back with xl95, macros were saved in macro sheets (before the VBE was invented). Any chance you're saving as xl95? You should be able to save as a normal workbook--the file format for xl97, xl2k, xl2002, xl2003 are all the same. === When bad things happen to good modules, maybe it's time to run Rob Bovey's code cleaner: You can find it he http://www.appspro.com/ Kate wrote: I'm attempting to re-program a workbook which I originally designed in Excel 2003, to run in Excel 97 also. I've discovered a few annoying items, such as worksheet-changes not being triggered when data validation drop boxes are used. However, this one has me bamboozled: Modules randomly appear as tabs in the worksheet. When this happens, the whole thing locks up and must be crashed out-of. This only happens with stand-alone modules, not with code that is associated with each sheet. Does anyone have any idea how to resolve this issue? I need these modules to be available to all the sheets at all times. Thanks, in advance, Kate -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97 modules appear as tabs
Dave, thanks for that quick reply. I indeed had originally
saved it in Excel 2003 as Excel 97 -Excel 2003 and 5 & 95 workbook. Based upon this information, I have re-saved it in Excel 97 as just Microsoft Excel Workbook, and I seem to not be having that problem any more!! Thank you so much! I will also keep that code cleaner in mind, should other problems arise... -Kate Dave Peterson wrote: I've never seen this problem. But back with xl95, macros were saved in macro sheets (before the VBE was invented). Any chance you're saving as xl95? You should be able to save as a normal workbook--the file format for xl97, xl2k, xl2002, xl2003 are all the same. === When bad things happen to good modules, maybe it's time to run Rob Bovey's code cleaner: You can find it he http://www.appspro.com/ Kate wrote: I'm attempting to re-program a workbook which I originally designed in Excel 2003, to run in Excel 97 also. I've discovered a few annoying items, such as worksheet-changes not being triggered when data validation drop boxes are used. However, this one has me bamboozled: Modules randomly appear as tabs in the worksheet. When this happens, the whole thing locks up and must be crashed out-of. This only happens with stand-alone modules, not with code that is associated with each sheet. Does anyone have any idea how to resolve this issue? I need these modules to be available to all the sheets at all times. Thanks, in advance, Kate |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97 modules appear as tabs
Dave, after I thought I'd fixed the problem, I continued to
encounter other errors that seemed related to the same problem, that Excel 97 thinks the modules are worksheets. I have numerous cases in which I loop through all the worksheets in the workbook, such as turning protection off or on in order to make some changes to protected cells. The sheet counter moves past the last sheet, and goes into the modules, giving a type mismatch error. e.g.: Set wb = ActiveWorkbook For Each sht In wb.Sheets setProtection sht, False Next sht Public Sub setProtection(wks As Worksheet, bStatus As Boolean) With wks If bStatus Then .EnableSelection = xlUnlockedCells .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Else .Unprotect End If End With End Sub -Kate Dave Peterson wrote: I've never seen this problem. But back with xl95, macros were saved in macro sheets (before the VBE was invented). Any chance you're saving as xl95? You should be able to save as a normal workbook--the file format for xl97, xl2k, xl2002, xl2003 are all the same. === When bad things happen to good modules, maybe it's time to run Rob Bovey's code cleaner: You can find it he http://www.appspro.com/ Kate wrote: I'm attempting to re-program a workbook which I originally designed in Excel 2003, to run in Excel 97 also. I've discovered a few annoying items, such as worksheet-changes not being triggered when data validation drop boxes are used. However, this one has me bamboozled: Modules randomly appear as tabs in the worksheet. When this happens, the whole thing locks up and must be crashed out-of. This only happens with stand-alone modules, not with code that is associated with each sheet. Does anyone have any idea how to resolve this issue? I need these modules to be available to all the sheets at all times. Thanks, in advance, Kate |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97 modules appear as tabs
You could be more selective in your code:
Set wb = ActiveWorkbook For Each sht In wb.Worksheets setProtection sht, False Next sht If I remember correctly, you can try this to fix the problem. Open the problem workbook start a new workbook (just for temporary use) Get to the VBE (alt-f11) show the project explorer so you can see the Modules of the problem workbook. Drag each module from the problem project to the temporary workbook's project (just click and drag it). After you've copied over all the modules, delete those modules in the problem workbook. (Just rightclick on each and choose remove module--don't bother exporting it.) Then drag the copied modules back from the temporary workbook's project back to the troubled workbook. close and save your problem workbook. (close without saving the temp workbook.) Now reopen your "used-to-be" problem workbook. If you don't trust this, make a backup copy first! (I just tried it from an oldddddddd workbook (last updated in 1996) and it seemed to work ok!) Kate wrote: Dave, after I thought I'd fixed the problem, I continued to encounter other errors that seemed related to the same problem, that Excel 97 thinks the modules are worksheets. I have numerous cases in which I loop through all the worksheets in the workbook, such as turning protection off or on in order to make some changes to protected cells. The sheet counter moves past the last sheet, and goes into the modules, giving a type mismatch error. e.g.: Set wb = ActiveWorkbook For Each sht In wb.Sheets setProtection sht, False Next sht Public Sub setProtection(wks As Worksheet, bStatus As Boolean) With wks If bStatus Then .EnableSelection = xlUnlockedCells .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Else .Unprotect End If End With End Sub -Kate Dave Peterson wrote: I've never seen this problem. But back with xl95, macros were saved in macro sheets (before the VBE was invented). Any chance you're saving as xl95? You should be able to save as a normal workbook--the file format for xl97, xl2k, xl2002, xl2003 are all the same. === When bad things happen to good modules, maybe it's time to run Rob Bovey's code cleaner: You can find it he http://www.appspro.com/ Kate wrote: I'm attempting to re-program a workbook which I originally designed in Excel 2003, to run in Excel 97 also. I've discovered a few annoying items, such as worksheet-changes not being triggered when data validation drop boxes are used. However, this one has me bamboozled: Modules randomly appear as tabs in the worksheet. When this happens, the whole thing locks up and must be crashed out-of. This only happens with stand-alone modules, not with code that is associated with each sheet. Does anyone have any idea how to resolve this issue? I need these modules to be available to all the sheets at all times. Thanks, in advance, Kate -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 97 modules appear as tabs
Thanks again, Dave. I'll give it a try later. Time to head
off for the office xmas party now! -Kate Dave Peterson wrote: You could be more selective in your code: Set wb = ActiveWorkbook For Each sht In wb.Worksheets setProtection sht, False Next sht If I remember correctly, you can try this to fix the problem. Open the problem workbook start a new workbook (just for temporary use) Get to the VBE (alt-f11) show the project explorer so you can see the Modules of the problem workbook. Drag each module from the problem project to the temporary workbook's project (just click and drag it). After you've copied over all the modules, delete those modules in the problem workbook. (Just rightclick on each and choose remove module--don't bother exporting it.) Then drag the copied modules back from the temporary workbook's project back to the troubled workbook. close and save your problem workbook. (close without saving the temp workbook.) Now reopen your "used-to-be" problem workbook. If you don't trust this, make a backup copy first! (I just tried it from an oldddddddd workbook (last updated in 1996) and it seemed to work ok!) Kate wrote: Dave, after I thought I'd fixed the problem, I continued to encounter other errors that seemed related to the same problem, that Excel 97 thinks the modules are worksheets. I have numerous cases in which I loop through all the worksheets in the workbook, such as turning protection off or on in order to make some changes to protected cells. The sheet counter moves past the last sheet, and goes into the modules, giving a type mismatch error. e.g.: Set wb = ActiveWorkbook For Each sht In wb.Sheets setProtection sht, False Next sht Public Sub setProtection(wks As Worksheet, bStatus As Boolean) With wks If bStatus Then .EnableSelection = xlUnlockedCells .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True Else .Unprotect End If End With End Sub -Kate Dave Peterson wrote: I've never seen this problem. But back with xl95, macros were saved in macro sheets (before the VBE was invented). Any chance you're saving as xl95? You should be able to save as a normal workbook--the file format for xl97, xl2k, xl2002, xl2003 are all the same. === When bad things happen to good modules, maybe it's time to run Rob Bovey's code cleaner: You can find it he http://www.appspro.com/ Kate wrote: I'm attempting to re-program a workbook which I originally designed in Excel 2003, to run in Excel 97 also. I've discovered a few annoying items, such as worksheet-changes not being triggered when data validation drop boxes are used. However, this one has me bamboozled: Modules randomly appear as tabs in the worksheet. When this happens, the whole thing locks up and must be crashed out-of. This only happens with stand-alone modules, not with code that is associated with each sheet. Does anyone have any idea how to resolve this issue? I need these modules to be available to all the sheets at all times. Thanks, in advance, Kate |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Public, Private, Event modules, Forms modules,,, | Excel Programming | |||
Basic question - modules and class modules - what's the difference? | Excel Programming | |||
When to code in sheet or userform modules and when to use modules | Excel Programming | |||
old makro (excel 95) should work with vba-modules | Excel Programming | |||
Class Modules vs Modules | Excel Programming |