Solved

Worksheet randomly freezes on on close when using exit button.

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

839 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