How to fix mystery sheet

[JLGWhiz]

Folks, here is one for the books.

I have an old VBA program that I was updating and
wanted to use a Worksheet.Add statement, but kept
getting an error message that the Add method failed.

After some debugging, I found that the sheet count
for the file was four, although only three sheets
were visible. Thinking that this would be easy to
fix, I proceeded to unhide any hidden sheets. There
were no hidden sheets.

It took a while to isolate, but finally, using a message
box: MsgBox Sheets(4).Name : I found where the other
sheet was. It was Module1. The next mystery is how did
this code module acquire the designation of Sheets(4)?
The name does not appear in the properties window for
any of the items listed in the project including module1.


Better yet, how do I get rid of it without losing the code
in the module? This is basically a rhetorical question
since I have already copied the code to another workbook
and it is performing normally. I am just curious about
how this could have happened and how it might be fixed.
The file was originated in 1999, using Windows95, Office
Suite, Excel5.0. I have no idea what I migh have done
back then that would have created this condition, although
there was evidence that I had added and deleted sheets
previously, leaving the sheet names and code names out
out of sequence.

 

[Chip Pearson]

Before a real VBA editor with real modules was introduced in Excel 97,
VBA code was stored in Module Sheets. What you found was basically
that.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[JLGWhiz]

Thanks for the come back, Chip. I was pretty sure it was age related, but I
was not really into the technology back then. I had just learned VBA out of
necessity to convert some old Macro 4.0 files over since Microsoft has made
it known they would stop supporting the Macro code.
I did remember that it was not like the VBA of 2003 version that I began
using when I got back into writing code. Again, thanks for the memory
jogger.