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

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

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

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

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

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