Run VBA Macros When A 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)

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.  

Hidden Hacks For VBA Macro Coding

After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!

Learn These Hacks Now For Free!

Keep Learning

Explore more →

VBA Code To Activate A Specific Worksheet When An Excel Workbook Opens

What This VBA Code Does This macro code will ensure a specific worksheet tab is viewed every time the Excel...

Prevent Your VBA Code From Running While In Read-Only Mode

What This Does I had an instance where I needed to prevent users from running a workbook's macros unless the...

How To Trigger Your VBA Macros To Run Based On A Specific Cell Value Change

Today we are going to discuss how you can automatically make your VBA code execute based on a specific cell...

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.