Run VBA Macros When A Spreadsheet Opens or Closes

how to run vba macros when your spreadsheet opens or closes

Triggering A Macro Via An Event

There are many events that Excel keeps track of while you carry out tasks in your workbook.  Events that are tracked range from tracking if you saved your project to any change in a cells value.  By triggering your VBA code based on a specific event done by a user you can do some pretty nifty things.  

In this post we will focus on the Open and BeforeClose events.  The image below shows how you can navigate to the Workbook Object within the Visual Basic Editor. The Workbook Object is where workbook events can be accessed through VBA code.

Steps To Access Workbook Events With VBA

  1. Open the Visual Basic Editor (shortcut key: Alt+F11)
  2. Locate your Workbook Project in the Project Window and double click on ThisWorkbook within the Microsoft Excel Objects folder
  3. Select Workbook from the Object drop down (first drop down)
  4. Insert any event subroutine by selecting your desired event from the Procedure drop down menu (second drop down)
Workbook Events Object & Procedure Drop Downs

Adding Macro Code To A Workbook Event

Once you have your desired subroutine declared, you can then add some VBA code within the Sub statement to carry out what ever tasks you want to occur when the respective event is triggered by the user.  This can be a Call to another subrountine or you can simply write your macro within the event procedure.

The below code displays a message box to the user when the workbook is opened and right before the workbook closes. Environ("Username") retrieves the user's computer login name.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

  MsgBox "Goodbye " & Environ("Username") & "!"

End Sub

Private Sub Workbook_Open()

  MsgBox "Hello " & Environ("Username") & "!"

End Sub

The following code runs a macro called AddTodaysDate when the workbook is opened.

Private Sub Workbook_Open()

  Call AddTodaysDate

End Sub

What Will You Use Events For?

I use events for a lot of different reasons.  I've used event triggers to make sure a workbook is password protected before it is closed and there have been instances where I have used events to automatically open up specific files when the workbook is opened.  There are so many really creative ways you can use events to automate your tasks.  I want to hear from you and learn what you use event triggers for!  I look forward to reading your comments.  

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)