Many times we want to do some predefined and repetitive steps before the workbook closes. For example, closing connections to the database, deleting garbage data, protecting the workbook, saving the workbook or simply saying bye to the user. This can be done using Workbook Events of Excel.
In this article we will learn how you can run a macro as the workbook closes in the most easy way.
To run macro as the workbook closes we will use Workbook Event Workbook_BeforeClose. The syntax of this event is this.
Private Sub Workbook_BeforeClose(Cancel As Boolean) 'Your Code here. End Sub
To write in a workbook event do this.
Open VBE using Alt+F11. Now in project explorer, locate the workbook in which you want to have the code. Now find ThisWorkbook object in project explorer. Double click on it.
Now you can see two drop down above the coding area. Click on the first one and select
Workbook. Now all the available events related to the workbook can be seen in the right drop down. Click on it and choose BeforeClose. This will write an empty workbook_BeforeClose sub routine on the coding area.
All the code that you want to run before the workbook closes needs to be written here. You can simply call other subroutines written in any module, in this event to run. This code will trigger the event to run anything written between these two lines.
Example:
Let's use the Workbook_BeforeClose event to verify a user and greet the user as the workbook opens.
Private Sub Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook.Save MsgBox "This Workbook is saved." End Sub
You can put anything in this code. We can even call any subroutine from any module in the workbook.Save the workbook and run the code once. Now close the workbook. Your workbook will be automatically saved and the message will be shown.
Private Sub Workbook_BeforeClose(Cancel As Boolean) Call ShutDatabase ' calling subroutine that shuts down the source database End Sub
So yeah, that is it. It is this simple way to auto run a macro as the workbook closes. I hope my writing was explanatory enough and the site served you well. If this didn't help, write your query in the comments section below. I will reply to it as soon as possible.
Related Articles:
Workbook events using VBA in Microsoft Excel : Here you can find all the workbook events that can be triggered to run specific code on specific events.
Using Worksheet Change Event To Run Macro When any Change is Made | So to run your macro whenever the sheet updates, we use the Worksheet Events of VBA.
Run Macro If Any Change Made on Sheet in Specified Range | To run your macro code when the value in a specified range changes, use this VBA code. It detects any change made in the specified range and will fire the event.
Simplest VBA Code to Highlight Current Row and Column Using | Use this small VBA snippet to highlight the current row and column of the sheet.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make your work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
