Force Excel to Save Workbook before Closing using Excel VBA

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 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). 

  • 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 

Force Excel to Save Workbook before Closing using Excel VBA 1

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.
Force excel to save workbook before closing
Download the Example Workbook here

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! 

FREE Advanced Excel Ebook

Get This Amazing PRO Excel & Excel VBA Tips and Tricks Ebook worth $ 30 for FREE ! Apply Ready to Use Formulas and VBA code to your requirements and work faster and smarter!

Check out my other related posts here…

Picture of Prashant
Prashant
Automation, Data Modeling & Data Science Enthusiast | Online Instructor | Tech Geek

Leave a Reply

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

Hey there, there are some great free eBooks available that can help you improve your skills in Excel, reporting, and accounting. Would you like to check them out?

New Gallery