Solved

Worksheet randomly freezes on on close when using exit button.

Posted on 2012-03-12
3
234 Views
Last Modified: 2012-03-15
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.
0
Comment
Question by:mato01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 6

Expert Comment

by:reitzen
ID: 37713287
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.
0
 
LVL 9

Expert Comment

by:suvmitra
ID: 37713309
put an error handler in the BeforeClose event of ThisWorkbook

On Error Resume Next
Thisworkbook.Save
0
 
LVL 6

Accepted Solution

by:
reitzen earned 100 total points
ID: 37713348
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question