Excel Macro: Auto Save a Workbook Before Closing

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

Excel normally warns 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 close.

Auto Save a Workbook Before Closing

'------------------ ThisWorkbook ------------------
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, cancel the close
    Case Is = vbCancel
    Cancel = True

'Step 3: OK button pressed, save the workbook and close
    Case Is = vbOK
    ActiveWorkbook.Save

'Step 4: Close your Select Case statement
    End Select

End Sub

How This Macro Works

This 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 user whether he really wants to close the workbook. The macro then evaluates whether the user clicked OK or Cancel.

1. In Step 1, we activate the message box as the condition check for the Select Case state-ment. Here, we use the vbOKCancel argument to ensure that the OK and Cancel buttons are presented as choices.
2. If the user clicked Cancel in the message box, the macro tells Excel to cancel the Workbook_Close event. This is done by passing True to the Cancel Boolean.
3. If the user clicked OK in the message box, Step 3 takes effect. Here, we tell Excel to save the workbook. And because we did not set the Cancel Boolean to True, Excel continues with the close.
4. In Step 4, we 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 This Macro