Macro 3: Saving 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.
How it 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 (see Figure 2-2). The macro then evaluates whether the user
clicked OK or Cancel.
Figure 2-2: A message box activates when you attempt to close the workbook.
The evaluation is done with a Select Case statement. The Select Case statement is an alter-
native 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, we 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, 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
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 it
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 the Visual Basic Editor by pressing ALT+F11 on your keyboard.
2. In the Project window, find your project/workbook name and click the plus sign next to it in
order to see all the sheets.
3. Click ThisWorkbook.
4. Select the BeforeClose event in the Event drop-down list (see Figure 2-3).
5. Type or paste the code in the newly created module.
Figure 2-3: Type or paste your code in the Workbook_BeforeClose event code window.
