Microsoft KB Archive/213606

= XL2000: Excel Hangs When You Programmatically Save a Workbook =

Article ID: 213606

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213606





SYMPTOMS
When you save a workbook in Microsoft Excel, the program may appear to stop responding (hang), and the following symptoms may occur:


 * The screen does not redraw properly.

-and-


 * You are unable to select cells, menus, toolbar buttons, or other controls.

-and-


 * Parts of message boxes and other windows remain on the screen on top of the Microsoft Excel window.



CAUSE
This problem occurs when all of the following conditions are true:

  You run a Microsoft Visual Basic for Applications macro that contains a command similar to the following

ActiveWorkbook.OnSave = ""

where  is the name of a macro.

-and-   The macro name you specify in the OnSave command contains the following Visual Basic macro code:

Application.ScreenUpdating = False

-and-  The macro you specify in the OnSave command does not reset the ScreenUpdating property to True.



RESOLUTION
To prevent this problem from occurring, add the following code to all macros that you specify in the OnSave command

Application.ScreenUpdating = True

before the end of each macro.

NOTE: When this behavior occurs, you must quit Microsoft Excel by pressing ALT+F4.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

<div class="moreinformation_section">

MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

In Microsoft Excel 7.0 and later, you can use the OnSave property to specify a macro that runs after you initiate saving a workbook, and before Microsoft Excel saves the workbook.

Example of the Problem
The following example demonstrates the use of the OnSave property:

Sub SetOnSaveTrigger ActiveWorkbook.OnSave = "WorkbookSaving" End Sub

Sub WorkbookSaving(Arg As Boolean) Application.ScreenUpdating = False MsgBox "Now saving file!" End Sub

After you run the first macro, Microsoft Excel automatically runs the second macro each time you save the active workbook.

Turning off Screen Updating
You can turn off screen updating while a macro runs by including the following line of code in the macro:

Application.ScreenUpdating = False

You can enable screen updating by setting the ScreenUpdating property to True. Usually, this step is not necessary: Microsoft Excel automatically enables screen updating when the macro stops running. However, there is one exception to this rule. If a macro that is run by the OnSave property contains a command to turn off screen updating, Microsoft Excel does not enable screen updating automatically when the macro is finished.

When this behavior occurs, Microsoft Excel appears to stop responding (hang). However, the program is not "frozen." The program is not redrawn on the screen correctly, which prevents you from using any part of the program.

Example of the Resolution
To prevent the problem, enable screen updating by resetting the ScreenUpdating property to True before the end of the macro. For example, change the example in the "Example of the Problem" section to the following:

Sub WorkbookSaving(Arg As Boolean) Application.ScreenUpdating = False MsgBox "Now saving file!" Application.ScreenUpdating = True End Sub

Additional query words: hung freeze frozen locked stopped XL2000

Keywords: kbbug kbdtacode kbpending kbprogramming KB213606

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.