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.
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.
- 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.
- 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.
- 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.
- 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
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.
- Activate the Visual Basic Editor by pressing
- In the Project window, find your project/workbook name and click the plus sign next to it in order to see all the sheets.
- Click ThisWorkbook.
- Select the BeforeClose event in the Event drop-down list.
- Type or paste the code in the newly created module.