EXCEL 97 - Application error when trying to add sheet.

[Kevin Rudd]

System : DELL GX150 with 256Mb RAM
OS: Windows NT Workstation with service pack 6.0
APPLICATION : EXCEL 97

Problem:
I have a workbook that has some sheets in it that has
predefined drop down boxes throughout the sheet. When I
try to copy a sheet to the end of the workbook I get the
error shown below. Sometimes it happens after 35
worksheets and sometimes after only 12 or 14.


DR. Watson for Windows NT

An application error has occured
and an application error log is being generated.

EXCEL.exe
Exception access violation (0xc0000005), Address:
0x303a500f

 

[Jan Karel Pieterse]

Hi,

This may be caused by the infamous long Codenames bug in
Excel97.

A worksheet has two names.

1. The one you see on its tab
2. the Code name you can see in the Visual Basic Exditor.

The bug surfaces when you copy a sheet. XL97 adds a 1 to
the code name of the copy, so it's Codename becomes
Sheet11. Copying that copy gives you Sheet111, etcetera.
After a numer of copies of copies, Excel 97 reaches its
limit of the allowed length for the codename and crashes
upon the next copy.

Remedy:

Do not copy the copy of the copy, but just copy the same
sheet every time.

To fix your current book:

1. Open the file in Excel 97
2. Open the VBE (Alt-F11)
3. Find the file's project in the project explorer
4. Expand the project
5. Make sure the properties window is visible (view menu)
6. Click a sheet
7. Check out the (Name) property, if it looks like
Sheet11111111111111111, change it to something shorter
(and unique!)
8. Repeat step 6 for all sheets
9. Close the VBE
10.Save the workbook

Regards,

Jan Karel Pieterse
Excel TA/MVP