Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Worksheet randomly freezes on on close when using exit button.

Posted on 2012-03-12
3
Medium Priority
?
245 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 400 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

688 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