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.
Discover how you can automate your work with our Excel courses and tools.
The Excel Academy
Make working late a thing of the past.
The Excel Academy is Excel training for professionals who want to save time.
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