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.

Macro 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

'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

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.
  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.Workbook BeforeClose Event
  5. Type or paste the code in the newly created module.

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>

  1. BE

    I have this code, and i need the save before close to have the same address, can you help

    Private Sub CommandButton1_Click()
    Dim Path As String
    Dim FileName1 As String
    Dim FileName2 As String
    Path = "C:\best\"
    FileName1 = Range("R2")
    FileName2 = Range("E8")
    ActiveWorkbook.SaveAs Filename:=Path & FileName1 & "-" & FileName2 & "-" & ".xlsm", FileFormat:=52

    End Sub

  2. PT
    Peter Turnbull

    The macro works amazingly well - but how do I universally apply it to every workbook I open without manually adding it?

    Thank you,