Solved

Worksheet randomly freezes on on close when using exit button.

Posted on 2012-03-12
3
230 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
  • 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now