Cengca

Excel Macro Tips – How To Run A Macro Automatically When You Open Your Excel Workbook

[ad_1]

This article will explain a few different ways to get an Excel Macro to run automatically when you open an Excel file.

The simplest way to get a macro to run automatically is to actually just name that macro Auto_Open in the work book you want to open. You can follow the steps below to achieve this.

First ensure the Developer Tab is enabled. 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 Work book.. When you select this option then Excel creates (if it is not already created) this work book and saves the macro in that location, by default this macro work book is named Personal.xlsb.

  • 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 need to find where your XLStart folder is then you can do so by (Excel 2013)

  • File
  • Options
  • If you want this Auto_Open macro to be available to run automatically in another work book then you need that work book to be saved in the XLStart folder so that both workbooks are opened when Excel starts
  • 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

  1. If the work book where Auto_Open is stored contains a VBA procedure in its Open event then that procedure will override the Auto_Open macro.
  2. As Auto_Open runs before any other workbooks are opened, if you have recorded actions that you want Excel to perform on the default work book 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 work books are opened.
  3. Auto_Open will not run if you open a work book programmatically.

If you do not want the Auto_Open macro to run when opening your Excel file, then 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
  • Open the work book where you want to save your macro
  • Developer Tab – Code Group – Visual Basic
  • Project Explorer Window
  • Right click This Work book
  • View Code
  • From the Object List Select Work book – an empty procedure is automatically created that looks like this

Add your code to the procedure in this for example add today’s date to Cell A1

  • Switch back to Excel and save the file with the .xlsm extension (macro)
  • Close your work then re -open your procedure should run

Choose the right solution for yourself, either to simply save the macro as Auto_Open, or create and Open Event procedure in VBA.

[ad_2]

You may also like...