excel tip Archives

Hello Excellers and welcome back to another #MacroMonday #Excel blog post in my 2020 series. So, today I will show you a number of different ways to automatically run a VBA macro when you open your Excel workbook. Method 1. Name Your Excel Macro Auto_Open The simplest way to get a macro to run automatically is to actually just name the macro Auto_Open. This first method does have limitations which will be covered in its explanation below. The macro needs to be stored in the workbook you want to open should you choose to use this method. Follow the simple 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 YouTube video here, or click on the link below. Initially, you may get a warning about Macro Security. If this happens then you need to set the security level to enable all macros. Follow the steps below to do this. Developer Tab Code Group Macro Security Hit Enable All Macros (I 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 running without notification). Open the workbook you want to save the macro in Hit Record Macro Type the name Auto_Open in the Macro Name dialogue box In the Store Macro list- you need to decide where you want the macro to be saved Note. Store the macro in your Personal Macro Workbook if you want this macro to be available when you whenever you open Excel. 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 then I recommend my blog posts below. Macro Mondays -Creating and Updating Your Personal Macro Workbook Why Is My Personal Macro Workbook Not Loading Automatically? Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook Recording Your Excel VBA Macro. Click Ok, then start to record 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) File Options Trust Centre Trust Centre Settings Trusted Locations - you will find it in here Method 1 Limitations. There are a few 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 override 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 programmatically. 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 they occur? Method 2. Create a VBA procedure for the Open Event All you need to do to avoid these limitations is to 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 procedure is automatically created that looks like this Add your code to the procedure in this example I have added today's date to Cell A1. Very simple. Switch back to Excel and save the workbook with the .xlsm extension (macro) Close your workbook then reopen your procedure should run These are two alternative ways to automatically run and Excel VBA Macro when your workbook opens. If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips. Likewise, if you want to see all of the blog posts in the Formula Friday or Macro Monday Series Click The Links Below Macro Mondays Blog Posts. Formula Friday Blog Posts. So, Don't forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.