When you programmatically save a workbook in Excel, a menu command does not run in the BeforeSave event (898511)


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002
  • Microsoft Excel 2000

SYMPTOMS

You programmatically save a workbook in Microsoft Office Excel 2003. If you try to perform a menu command in an event, such as the BeforeSave event, the menu command does not run.

CAUSE

This issue occurs because a menu command cannot be nested in the code when you programmatically call a menu command such as the Save command. The menu command in the BeforeSave event is specifically blocked from running.

WORKAROUND

To work around this issue, manually save the workbook. To manually save the workbook, click Save on the File menu. When you use this workaround, you can perform any one of the following menu commands in the BeforeSave event:
  • Any command on the Format menu.
  • The Clear command on the Edit menu.
  • The Delete command on the Edit menu.
  • The Delete rows command on the Edit menu.
  • The Print command on the File menu.

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. To reproduce this issue, follow these steps:
  1. Open a new workbook in Excel.
  2. Type some text in the A1 cell, and then press ENTER.
  3. On the Tools menu, point to Macro, and then click Visual Basic Editor.
  4. In the Project - VBAProject pane, double-click ThisWorkbook.
  5. Follow these steps:
    1. In the Object box, click Workbook.
    2. In the Procedure box, click BeforeSave.
    3. Add the following macro code to the BeforeSave event: 
         Range("a1").ClearContents
    4. Under the macro code that you typed in step 5c, type the following macro code: 
      Public Sub Test()

   ThisWorkbook.Save

End Sub
  6. In the workbook, click Save on the File menu.

    The text in the A1 cell is cleared.
  7. Click Cancel to close the Save dialog box.
  8. Type some text in the A1 cell, and then press ENTER.
  9. On the Tools menu, point to Macro, and then click Macros.
  10. Click the ThisWorkbook.Test macro, and then click Run.

    The text in the A1 cell is not cleared when the workbook is saved.
Modification Type:MajorLast Reviewed:7/19/2005
Keywords:kbmacro KbVBA kbProgramming kbAutomation kbtshoot kbprb KB898511 kbAudEndUser kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.