Excel Crashing which is prevented by unprotecting VB code

[Sssuperdave]

I've been asked to troubleshoot a workbook for another area of my company -
the problem they are having is quite strange. The workbook is a fairly
complicated tool that inwith a number of forms and inputs, and interacts
with a WebFocus table, that will ultimately be used by a number of 3rd
parties.

After filling in a particular form excel will always completely crash,
giving the "Do you want to send an error report to Microsoft..." dialog. It
is quite strange because at teh time of the crash this form is doing nothing
special. It just copies some values from the form to a worksheet and then
calls another form.

Here is the strangest part. The VB code is protected. If I unprotect the VB
code prior to opening the form, the error does not happen, and the workbook
works flawlessly. Again, that is unprocting the VB code itself, not the
workbook or any worksheets. I haven't been able to find any other way to
keep the error from happening.

The version of excel that I'm using is 2003. Any ideas?

 

[Bob Flanagan]

What version of Excel? Have you installed all the service packs and then
done a repair of Office (SPs can screw up Office, so I always do a repair
after installing SPs). Have you tried it on other machines?

Try setting Application.ScreenUpdating = True before the form is displayed.

Does it crash when you step through the code? I suspect not as you say it
doesn't crash with the vb editor displayed. Try putting in a bunch of
messages until you identify the line of code causing the crash. Post it!

How are you closing the form? Via Me.Hide?

If you are unloading the form after using, don't unload it. Just hide it.

Create a new userform. On the old form select and copy (not cut) the
controls to the new form. Copy the code to the new form. Delete the old
form. Rename the new form to the old form name. I've had a few corrupt
forms lock up Excel (not crash).

Are you displaying multiple forms at one time? Its usually a bad idea to do
so.....

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[Jan Karel Pieterse]

Hi Sssuperdave,

After filling in a particular form excel will always completely crash,
giving the "Do you want to send an error report to Microsoft..." dialog. It
is quite strange because at teh time of the crash this form is doing nothing
special. It just copies some values from the form to a worksheet and then
calls another form.

Here is the strangest part. The VB code is protected. If I unprotect the VB
code prior to opening the form, the error does not happen, and the workbook
works flawlessly. Again, that is unprocting the VB code itself, not the
workbook or any worksheets. I haven't been able to find any other way to
keep the error from happening.

Have you tried to clean the code?

http://www.appspro.com/Utilities/CodeCleaner.htm

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Sssuperdave]

Thank you for the advice Bob! Your idea about the corrupted userform did the
trick.