This post may contain affiliate links. Please read my disclosure for more info:

Automatically run a Macro when opening a workbook

Automatically run Macro when opening a workbook

Automatically run Macro when opening a workbook

There are a lot of circumstances where you need a Macro to run when a workbook is opened.  It could be one of the following:

  • Show a user form which requires input
  • Activate a specific range or object
  • To run a process which updates the data
  • Check the username of the person accessing the file
  • Automatically update cell values
  • Show an information message or instructions

Whilst this list contains some of the most common reasons, there are almost an endless number of circumstances where this would be useful.

Within this area there are two different concepts:

  1. Run the macro when opening the workbook which contains the macro.
  2. Run a macro when any workbook is opened.

Whilst these may sound similar the process required is slightly different.

Note: You may need to enable the Developer Ribbon to follow this tutorial.

 

Run the macro when opening the workbook which contains the Macro

This is the easier of the two circumstances.

Open the Visual Basic Editor (Short Cut: Alt+F11).

Run Macro on Open VBE Editor

Double click on This Workbook from the Project Explorer window

Run Macro on Open Workbook

Enter the code below into the code window.

Private Sub Workbook_Open()

MsgBox "You have just opened " & ThisWorkbook.Name

End Sub

Save the file as a Macro-enabled workbook (with a .xlsm file extension), and close the file.

Open the workbook again – ta-dah!!! The Macro should run automatically.

Run Macro on Open Workbook Message

 

Run Macro when any workbook is opened

For this code to work it is necessary to create a variable, which constantly monitors the Excel application.  The monitoring of the application starts as soon as the workbook is opened, which occurs it does so in the background.  Nothing will happen until you open another workbook.

Open the Visual Basic Editor (Short Cut: Alt+F11) – as shown in the above example.

Double click on This Workbook from the Project Explorer window

Run Macro on Any Workbook Open

Enter the code sections below into the code window.

'Declare the application event variable
Public WithEvents MonitorApp As Application
'Set the event variable be the Excel Application
Private Sub Workbook_Open()

Set MonitorApp = Application

End Sub
'This Macro will run whenever an Excel Workbooks is opened
Private Sub MonitorApp_WorkbookOpen(ByVal Wb As Workbook) 

MsgBox "You just opened " & Wb.Name  

End Sub

Save the file as a Macro-enabled workbook (with a .xlsm file extension), and close the file.

Open the workbook. It may seem like nothing is different to the first example above, but it is, you just can’t see it . . . yet.  Open some more workbooks.  You will see that this macro runs every time a workbook is opened.

Run Macro on Any Workbook Open MessageRun Macro on Any Workbook Open Message

If we open an Excel file without our macro file open nothing will happen.  However, if the macro file is open first it will then trigger the macro each time a workbook is opened.  If you wish to constantly monitor files being opened it is a good idea to include this macro within your Personal Macro book, as this file is open first when the Excel application is launched.

Leave a Reply

Your email address will not be published. Required fields are marked *