Automatically run a 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.


Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy
The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets
Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere
Start using Office Scripts and Power Automate to automate Excel in new ways.

8 thoughts on “Automatically run a Macro when opening a workbook”

  1. You have explained what to do with:
    1) Opening a Specific file and
    2) Opening ALL files

    What about files with different names but consistent ones? I have tried to adapt your code to this in my Personal file. I have searched a ton and tried on my own this afternoon but was unsuccessful. When users download and open .XLS files that begin with “Current Approved”, I want the macro to automatically run from the PERSONAL file on the”Current Approved* .XLS” file upon opening it. That way I can just put the code in the users Personal file, and it just automatically happens without them needing to trigger the macro.

    ‘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)

    Dim Wb2 As Workbook

    For Each Wb2 In Workbooks

    If Wb2.Name Like “Current Approved*” Then
    Wb2.Activate
    MsgBox “Test”
    End If
    Next

    End Sub

    Reply
    • Hi David,

      I think you were almost there.

      Wb is the variable which refers to the opened workbook, so you didn’t need to create a new Wb2 variable.

      Private Sub MonitorApp_WorkbookOpen(ByVal Wb As Workbook)
      
      If UCase(Wb.Name) Like "CURRENT APPROVED*.XLS" Then
          MsgBox "Test"
      End If
      
      End Sub
      
      Reply
  2. Hello,
    Curious if this might solve my problem. Currently, dealing with an excel file that has a pivot table, but now raw data to make file smaller. I can double click grand total to create a data sheet for Alteryx use. Alteryx must have the raw data. Wrote these macros to create data sheets. Could have have them auto run when Alteryx opens the excel file.

    Macro 1
    Sub TestPurchaseService()

    ‘ TestGrand Macro


    Sheets(“Purchase Service”).Select
    Range(“A12”).Select
    ActiveSheet.PivotTables(“PivotTable4”).PivotSelect “Region[All]”, xlLabelOnly, _
    True
    ActiveSheet.PivotTables(“PivotTable4”).ClearAllFilters
    Dim rLastCell As Range
    With ActiveSheet.PivotTables(1).TableRange1
    Set rLastCell = .Cells(.Rows.Count, .Columns.Count)
    End With
    rLastCell.Select
    Selection.ShowDetail = True
    Sheets(“Sheet1”).Select
    Sheets(“Sheet1”).Name = “Purchase_Service_for_Alteryx”
    End Sub

    Macro 2
    Sub TestGrand_Totals_Headcount()

    ‘ TestGrand Macro


    Sheets(“Headcount”).Select
    Range(“A12”).Select
    ActiveSheet.PivotTables(“PivotTable4”).PivotSelect “Region[All]”, xlLabelOnly, _
    True
    ActiveSheet.PivotTables(“PivotTable4”).ClearAllFilters
    Dim rLastCell As Range
    With ActiveSheet.PivotTables(1).TableRange1
    Set rLastCell = .Cells(.Rows.Count, .Columns.Count)
    End With
    rLastCell.Select
    Selection.ShowDetail = True
    Sheets(“Sheet2”).Select
    Sheets(“Sheet2”).Name = “Headcount_for_Alteryx”
    End Sub

    Reply
    • Sorry, but I’ve not used Alteryx before. It sounds like it should work as a solution to your problem.

      My advice is to keep experimenting until you find the solution.

      Reply
  3. Hello,

    Thank you for this great article!
    I made an add-in and I would like to include an “automatic macro” which runs every time a workbook is open once the add-in is installed; is it possible to save your code in the .add-in’s XLAM file instead of Personal.XLSB? I have not been able to get it to run successfully.

    Reply
    • Hi Carlos – Yes, the code can be in an .xlam file instead of a Personal Macrobook (Excel will work with both). If you follow the section entitled “Run Macro when any workbook is opened”, it should provide the answer you’ll need.

      If it is not working, are you getting an error message?

      Reply
  4. This works great for the most part, but does not seem to work when I open .csv files in excel. Is there a way to get this to work with any file opened in excel?

    Reply
  5. I have created an invoice nd have put in a button to Save & Clear” I am unsure of how to make it save each time I click the button this is what I have
    Sub NextInvoice()
    Range(“D4”).Value = Range(“D4”).Value + 1
    Range(“B13:D26”).ClearContents
    Range(“B7:B10”).ClearContents
    Range(“D7:D8”).ClearContents
    Range(“C28”).ClearContents
    Range(“D30:D31”).ClearContents
    Range(“B11”).ClearContents
    End Sub

    Private Sub CommandButton1_Click()
    Dim path As String
    Dim filename As String
    path = “C:\AAC\”
    filename1 = Range(“D4”).Text
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs filename:=path & filename1 & “.xlsx”, FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    ActiveWorkbook.Close

    any help would be appreciated

    Reply

Leave a Comment