Excel 97 crashes when button is clicked

[jon]

I had an Excel WB that was working fine, but I did something, and now it
dies horribly and consistently. It is, in fact, code that I intend to use
as an AddIn, but the problems described below occur when developing the AI
when it still is an XLS, not an XLA.

If I click on a button (a Shape, actually), Excel croaks with some kind of
GPF. This happens when I click any of a variety of such buttons (that is,
the problem appears to occur no matter which event is triggered). If I try
to use the VBA debugger to see which statement causes the problem, by
inserting a breakpoint on the first statement of the event handler, Excel
dies anyway, before the VBA debugger can get control.

I deduce, from this, that something is going wrong during some kind of
source code scan that is occurring before the event procedure is run. I've
looked for problems with the source code, like ill-formed subroutines or
functions, but no joy. No complaints from the VBA IDE. I don't know if
there is any way to have VBA scan the source code.

I have also seen another version of this WB that would not open in Excel 97
at all: it always croaked in the same manner as above. Such WBs can be
opened using Excel 2000, all of the code can be exported, and the resulting
new WB opens fine in Excel 97, but dies when buttons are clicked, as above.

I've looked at the MS web site to see if I should download some
patches/updates, but the MS site is far less than helpful. There are many
web pages that describe SP1, but no links to download that SP. You cannot
download SP2 unless you have SP1, but no way to download SP1. Catch 22.
Thanks MS. I found a table that implied that there may be no way to apply
ANY patches to the version of Excel 97 that I have, because it is too old
(it appears to be the original build). Thanks, again, MS.

Anyone seen this before? Any ideas what I can do to figure out what the
offending statement is, so that I can continue development?

Thanks

Jon

jbondy at sover dot net

 

[Sandy-V]

If the code was working previously it seems unlikely a
minor change requires updating of your XL97 (even though
it’s a good idea to do that).

As a first step try compiling the project (Debug >
Compile) and see if that flags any errors. If so, does it
also if you compile in XL2K.

Second step, exclude the possibility of a corrupted
workbook. Rebuild from scratch, copy sheets to a new wb
and drag or import modules from old to new. Might be
overkill but "Select all cells" and copy to worksheets,
rather than the lazy copy worksheets method. Check all
buttons with assigned macros are to code within the new
wb, not linked to the old.

I'm a bit confused about your "Buttons". AFAIK only
buttons from the "Control toolbox" menu could link to code
that could be described as event handlers. "Forms" buttons
and shapes with macros asigned (I think) can only link to
Sub's in normal modules. Also clicking a shape does not
trigger an event, other than calling the assigned macro.

Good luck,
Sandy

 

[Tom Ogilvy]

Try using Rob Bovey's code cleaner:

http://www.appspro.com

go to free downloads