Hello fellow Excellers and yes it is #MacroMonday time again. Time for some cool Macro fun. Today let’s look at the VBA BeforeClose event for a workbook. This event occurs before the workbook closes. If the workbook has been changed, this event occurs before the user is asked to save changes. We are going to use this event to remind users to make a backup copy of their file using the SaveAsCopy method, in conjunction with a Message Box as a prompt.

What Does The Macro Do?

This small routine or Macro uses a message box to ask the user if they would like to make a backup copy of the workbook they are working on. If the user answers yes then the code triggers the SaveAsCopy Method to save a backup copy of the file in a specified location.

How Does The Macro Work?

As this is a Workbook Event the code must be stored in that workbook. Select the BeforeClose event in the ThisWorkbook object.

Excel will automatically create a subroutine for you once selected.

So, now it is time to write that macro.

Step1. We need to declare some variables. This simply creates a memory container in Excel to store these values.

Dim Msg As String, Dim Ans As Integer, Dim FileName As String

This tells Excel they specific data types we are going to use in the procedure.

Step 2. Excel will display a standard message box to the user with a Yes and No option.

Step 3. If the user selects Yes then a copy of the file is placed in the specified file path. In this case, it is in a folder called “MyBackupFiles” on my C drive. You can tailor this to your own requirements.

Step 4. The routine then ends

Step 5. Test Your Macro. My favourite step!.

What Next? Want More Excel Tips?

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

If you want to see all of the blog posts in the Macro Monday series. Click on the link below

How To Excel At Excel – Macro Mondays Blog Posts.