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

Download the 100 Excel Macros ebook

100 Excel Macros Book

  • Contains 100 Excel VBA macros
  • Learn VBA by following along with the example codes
  • Apply to your macros, automate Excel, save time.

Download the ebook today!

Click the button below to subscribe, you’ll gain access to the subscriber area in which you can download the ebook.

Download Icon (on mid-green background)

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.

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

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

  1. David T Erickson says:

    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

    • Excel Off The Grid says:

      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
      
  2. Kevin Gordish PhD says:

    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

    • Excel Off The Grid says:

      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.

  3. Carlos says:

    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.

    • Excel Off The Grid says:

      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?

  4. Nathan says:

    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?

  5. C Dob says:

    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

Leave a Reply

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