This macro is an best way to protect users from accidentally closing their file before saving
You can Force excel to save workbook before closing using Excel VBA Code in at workbook level
If you are dealing with very sensitive data where information is really important and workbook you are using for this purpose is well protected, then you can force Excel to save workbook before closing.
While 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 accidentally click No, telling Excel to close without saving.
With this macro, you are forcing Excel to save before close which can be a life saver just in case you ignore Excel’s usual warnings
While you should keep in mind that over using this macro might result in annoyance to users…
How the code works?
This code is triggered by the workbook’s
BeforeCloseevent. 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).
The macro then evaluates whether the user clicked OK or Cancel
The evaluation is done with a Select Case statement. The Select Case statement is an alternative to the If…Then…Else statement, allowing you to perform condition checks in your macros.
With a Select Case statement, you can perform many conditional checks. In this case, we are simply checking for OK or Cancel
Below is the FINAL Code which will achieve our objective
Ready to use/apply VBA Code (double click to select entire 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 ThisWorkbook.Save 'Step 4: Close your Select Case statement End Select End Sub
How the code works- explained?
- In Step 1, we activate the message box as the condition check for the Select Case statement.
- 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 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.
- Activate the Visual Basic Editor by pressing ALT+F11 on your keyboard.
- 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 (see GIF Animation below).
- Type or paste the code in the newly created module.
Also check out my blog post on how to save workbook when you change a cell or range of cells automatically here
If you want to master Excel in Professional way, check out my FREE EBook available for download, this Ebook contains more of these ready to apply Excel VBA codes and formulas! (Just fill below form to get this amazing Ebook for FREE)
If you have any question or query regarding above code usage, let me know in the comments below!