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:
- Run the macro when opening the workbook which contains the macro.
- 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).
Double click on This Workbook from the Project Explorer window
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 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
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.
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.
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match 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:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- 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.
- 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:
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
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 SubHello,
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
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.
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.
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?
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?
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