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.
- 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.
Most VBA code should be placed in Standard Modules unless specified.
If you see a comment
'------------------ Modules------------------ in the code header that means put the code in a Standard Module. For more information, learn this course: Where should I put the Excel VBA code?
The following steps teach you how to put VBA code into a Standard Module:
- Activate the Visual Basic Editor by pressing ALT + F11.
- Right-click the project/workbook name in the Project Window.
- Choose Insert -> Module.
- Type or paste the code in the newly created module. You will probably need to change the sheet name, the range address, and the save location.
- Click Run button on the Visual Basic Editor toolbar.
- For more information, learn this course: Programming with Excel VBA