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

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

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.

To work around this issue, manually save the workbook. To manually save the workbook, use one of the following procedures, as appropriate for the version of Excel that you are running:

• In Microsoft Office Excel 2007, click the Microsoft Office Button, and then click Save.
• In Microsoft Office 2003 and in earlier versions of Excel, click Save on the File menu.

When you use this workaround, you can perform any 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.

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, as appropriate for the version of Excel that you are running.

Excel 2007

1. Open a new workbook in Excel.
2. Click the Microsoft Office Button, click Save As, click Excel Macro-Enabled Workbook (*.xlsm) in the Save as type box, type a file name in the File name box, and then click Save.
3. Type some text in cell A1, and then press ENTER.
4. Click the Developer tab. If the Developer tab is not displayed, follow these steps:
   1. Click the Microsoft Office Button, and then click Excel Options.
   2. Click Popular.
   3. Click to select the Show Developer tab in the Ribbon check box.
   4. Click OK to close the Excel Options dialog box.
5. Click Visual Basic in the Code group to start the Visual Basic Editor.
6. In the Project - VBAProject pane, double-click ThisWorkbook.
7. 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 7c, type the following macro code:

      Public Sub Test()
      
         ThisWorkbook.Save
      
      End Sub
      

8. In the workbook, click the Microsoft Office Button, and then click Save.
   
   The text in cell A1 is cleared.
9. Click Cancel to close the Save As dialog box.
10. Type some text in cell A1, and then press ENTER.
11. On the Developer tab, click Macros in the Code group.
12. Click the ThisWorkbook.Test macro, and then click Run.
    
    The text in cell A1 is not cleared when the workbook is saved.

Excel 2003 and earlier versions of Excel

1. Open a new workbook in Excel.
2. Type some text in cell A1, 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 cell A1 is cleared.
7. Click Cancel to close the Save As dialog box.
8. Type some text in cell A1, 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 cell A1 is not cleared when the workbook is saved.