×

Run VBA Macros When A Spreadsheet Opens or Closes

By Chris Newman •  Updated: 04/01/14 •  3 min read
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.

Run VBA Macros Spreadsheet Open Close Events

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.  


Keep Learning

Chris Newman

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.