Worksheet randomly freezes on on close when using exit button.

I have a worksheet with vba code that works great, except that sometimes if you use the X button to close the file, it will freeze and I get the error message "Microsoft Excel" has stopped working.  What could be the cause.  I don't get any errors when I debug the code.
mato01Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

reitzenCommented:
In your "ThisWorkbook" class you have a BeforeClose event.  If you cannot replicate when you are debugging, look at the order in which procedures are being called.  You may be trying to do something after/while the file is being saved.  Sometimes, in older versions of Excel adding a timer helps because it allows a process to finish before moving on to the next procedure.

If this produces no results, you can post your code or file to give us something to work with.
suvmitraCommented:
put an error handler in the BeforeClose event of ThisWorkbook

On Error Resume Next
Thisworkbook.Save
reitzenCommented:
suvmitra is right, that this may prevent the error from occurring, if you do not already have error handling in place.  If your ultimate goal is to prevent this or any error from occurring, then "On Error Resume Next" may prevent the error message, but will not resolve the problem at hand.  If the error happens to be in the "save" you'll lose your changes and your users will not be happy when this happens.

Typically, you want more exposure and better control as to what is happening in your application.  Work on finding the line/routine that is causing the error.  Your application will be much or solid if/when you do.

You can add more control when an error occurs by naming your own error handler.

Call out the error handler at the beginning of the procedure or when it is appropriate to do so:

On Error GoTo myErrorHandler

Open in new window


At then end of of the procedure, just before the "End Sub", add the closing/ending script first:

myErrorExit:
    msgbox "My message to my users goes here"
    Exit sub

Open in new window


Finally, you'll want to add your code to handle your error:
myErrorHandler:
    msgbox "An error occurred" err.number & " " & err.description
    ~add some additional error handling code here
    goto myErrorExit

Open in new window


You can keep an error log in your application or just simply let the user know when an error has occurred.  The extent/limit is up to you.

While this approach may not solve your problem, it may give you more insight into the issue and allow you to address it programatically.  Which will always allow you to provide a more professional solution.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.