An Excel Macro to Save a Workbook before Closing

By Michael Alexander

The macro presented here is an excellent way to protect users from inadvertently closing their file before saving. When implemented, this macro ensures that Excel automatically saves the workbook before closing it.

Excel will normally warn users who are attempting to close an unsaved workbook, giving them an option to save before closing. However, many users may blow past the warning and inadvertently click No, telling Excel to close without saving. With this macro, you are protecting against this by automatically saving before closing.

How the macro works

The code is triggered by the workbook’s BeforeClose event. When you try to close the workbook, this event fires, running the code within. The crux of the code is simple — it asks the users whether they want to close the workbook. The macro then evaluates whether the user clicked OK or Cancel.

The message you see when you try to close the workbook.

The message you see when you try to close the workbook.

The evaluation is done with a Select Case statement. The Select Case statement is an alternative to the If-Then-Else statement, allowing you to perform condition checks in your macros. The basic construct of a Select Case statement is simple:

Select Case <some expression to check>
Case Is = <some value>
       <do something>
Case Is=<some other value>
       <do something else>
Case Is=<some 3rd value>
       <do some 3rd thing>
End Select

With a Select Case statement, you can perform many conditional checks. In this case, you are simply checking for OK or Cancel. Take a look at the code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Step 1: Activate the message box and start the check
    Select Case MsgBox("Save and close?", vbOKCancel)
'Step 2: Cancel button pressed, so cancel the close
    Case Is = vbCancel
    Cancel = True
'Step 3: OK button pressed, so save the workbook and close
    Case Is = vbOK
'Step 4: Close your Select Case statement
End Select
End Sub

In Step 1, you activate the message box as the condition check for the Select Case statement. You use vbOKCancel argument to ensure that the OK and Cancel buttons are presented as choices.

In Step 2, if the user clicked Cancel in the message box, the macro tells Excel to cancel the Workbook_Close event by passing True to the Cancel Boolean.

If the user clicked the OK button in the message box, Step 3 takes effect. Here, you tell Excel to save the workbook. And because you didn’t set the Cancel Boolean to True, Excel continues with the close.

In Step 4, you simply close out the Select Case statement. Every time you instantiate a Select Case, you must close it out with a corresponding End Select.

How to use the macro

To implement this macro, you need to copy and paste it into the Workbook_BeforeClose event code window. Placing the macro there allows it to run each time you try to close the workbook:

  1. Activate Visual Basic Editor by pressing Alt+F11.

  2. In the project window, find your project/workbook name and click the plus sign next to it to see all the sheets.

  3. Click ThisWorkbook.

  4. In the Event drop-down list, select the BeforeClose event.

  5. Type or paste the code in the newly created module.

    Enter your code in the Workbook BeforeClose event.

    Enter your code in the Workbook BeforeClose event.