Macro Monday time again folks. Today let’s look at a couple of ways to get an Excel Macro to run automatically when you open an Excel workbook.
The simplest way to get a macro to run automatically is to actually just name that macro Auto_Open in the workbook you want to open. You can follow the steps below:-
First ensure the Developer Tab is enabled. If it is not you can follow my blog post here to enable it or watch my You Tube video here. You may get a warning about Macro Security, then you need to set the security level to enable all macros.
- Developer Tab
- Code Group
- Macro Security
- Hit Enable All Macros ( suggest you turn this back to one of the Disable All Macros settings when you are finished working with macros to prevent any dangerous code being ran without notification).
- Open the workbook you want to save the macro in
- Hit Record Macro
- Type the name Auto_Open in the Macro Name dialog box
- In the Store Macro list- you need to decide where you want the macro to be saved
Note- if you want your macro to be available whenever you open Excel, the you need to store the macro in the Personal Macro Workbook.. When you select this option then Excel creates (if it is not already created) this workbook and saves the macro in that location, by default this macro workbook is named Personal.xlsb.
If you want more details on creating and updating your personal macro workbook check out my blog post here.
- Click Ok and begin recording your macro
- After you have finished recording your macro you can either hit Developer Tab – Code Group- Stop Recording or
- Hit the Stop Recording Icon on the left hand side of the workbook Status Bar (this is the quickest method)
- If you want this Auto_Open macro to be available to run automatically in another workbook then you need that workbook to be saved in the XLStart folder so that both workbooks are opened when Excel starts
If you need to find where your XLStart folder is then you can do so by (Excel 2013)
- Trust Centre
- Trust Centre Settings
- Trusted Locations – you will find it in here
There are a few of limitations when using the Auto_Open method
- If the workbook where Auto_Open is stored contains a VBA procedure in its Open event then that procedure will overide the Auto_Open macro.
- As Auto_Open runs before any other workbooks are opened, if you have recorded actions that you want Excel to perform on the default workbook or one opened from the XLStart folder then Auto_Open will not run when you restart Excel as the macro runs before the default and startup workbooks are opened.
- Auto_Open will not run if you open a workbook programatically.
If you do not want the Auto_Open macro to run when opening your Excel workbook, the just hold down the SHIFT key when you are starting Excel.
So how do you get around these limitations if the occur?
All you need to do is create a VBA procedure for the Open Event –
- Ensure the Developer Tab is available See link above
- Open the workbook where you want to save your macro
- Developer Tab – Code Group – Visual Basic
- Project Explorer Window
- Right click This Workbook
- View Code
- From the Object List Select Workbook – an empty prodecure is automatically created that looks like this
- Add your code to the procedure in this example I have added todays date to Cell A1
- Switch back to Excel and save the workbook with the .xlsm extension (macro)
- Close your workbook then re -open your procedure should run
Dont forget to sign up to the Excel at Excel Newletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.