Running a macro when Excel starts

Applies to
Microsoft Excel 2000 and 2002

If you want to automatically perform certain actions whenever you start Microsoft Excel, you can record or write a macro that will run whenever you open a workbook. There are two ways to do this:

  • Record a macro and save it using the name Auto_Open.
  • Write the macro as a Microsoft Visual Basic® for Applications (VBA) procedure for the Open event of a workbook.

Recording an Auto_Open macro is subject to a number of limitations described later in this article. Writing a start-up macro as a VBA procedure for the Open event of a workbook is much more powerful and flexible than recording an Auto_Open macro and is the preferred method for creating a start-up macro.

 Important   To allow any macros to run in Excel 2000 or later, the security level must be set to either Medium or Low. To set the security level, on the Tools menu, point to Macro, and then click Macro. For information on macro security, see Help in your Excel program.

ShowSetting a workbook to open automatically when Excel starts

Using either method to create the macro requires you to save it in a particular workbook. As a result, if you want the macro to run whenever Excel itself is started, you must make sure that the workbook that contains the macro is automatically opened when Excel starts. To do that, you must save the workbook that contains the start-up macro (or a shortcut that points to that workbook) in the XLStart folder. The location of the XLStart folder varies depending on the version of Microsoft Windows® you are using and how it is configured.

Microsoft Windows 95 and Microsoft Windows 98

C:\Program Files\Microsoft Office\Office10\XLStart (for Office XP)
C:\Program Files\Microsoft Office\Office\XLStart (for earlier versions of Office)
C:\Windows\Application Data\Microsoft\Excel\XLStart

If you have Profiles enabled, then the path to the XLStart folder is instead:

C:\Windows\Profiles\username\Application Data\Microsoft\Excel\XLStart

where username is the name you use to log in to Windows.

Microsoft Windows NT® 4.0

C:\Program Files\Microsoft Office\Office10\XLStart (for Microsoft Office XP)
C:\Program Files\Microsoft Office\Office\XLStart (for earlier versions of Office)
C:\Winnt\Profiles\username\Application Data\Microsoft\Excel\Xlstart

where username is the name you use to log in to Windows.

Microsoft Windows 2000

C:\Program Files\Microsoft Office\Office10\XLStart (for Office XP)
C:\Program Files\Microsoft Office\Office\XLStart (for earlier versions of Office)
C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLStart

where username is the name you use to log in to Windows.

In all versions of Windows, it is also possible to specify your own folder for Excel start-up files in the At startup, open all files in box on the General tab of the Options dialog box (on the Tools menu, click Tools).

ShowCreating an Auto_Open macro

If you record a macro and save it using the name "Auto_Open", the macro will run whenever you open the workbook that contains the macro.

  1. If you want to save the macro with a particular workbook, open that workbook first.
  2. On the Tools menu, point to Macro, and then click Record New Macro.
  3. In the Macro name box, type Auto_Open.
  4. In the Store macro in box, specify where to save the macro:
    • To save the macro in the current workbook, choose This Workbook.
    • To create a new workbook to save the macro in, choose New Workbook.
    • To save the macro in a hidden workbook that is automatically loaded when you start Excel, choose Personal Macro Workbook.

 Note    If you choose Personal Macro Workbook, Excel will create and save the macro in a hidden workbook named Personal.xls (if Personal.xls doesn't already exist). In Windows 2000, Personal.xls is saved in the C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLStart folder so that it will be loaded automatically whenever Excel starts. If you want the macro you record and save in Personal.xls to act on a particular workbook, you must also save that workbook in the XLStart folder so that it will also be opened when Excel starts.

  1. Click OK, and then perform the actions you want to record.
  2. On the Stop Recording toolbar, click the Stop Recording button.
  3. If you chose to save the macro in This Workbook or New Workbook in step 4, make sure to save or move the workbook into one of the XLStart folders.

Recording an Auto_Open macro has a number of limitations:

  • Many actions you may want to perform cannot be recorded.
  • If the workbook in which you save the Auto_Open macro already contains a VBA procedure in its Open event, the VBA procedure for the Open event will override all actions in the Auto_Open macro.
  • An Auto_Open macro is ignored when a workbook is opened programmatically by using the Open method.
  • An Auto_Open macro runs before any other workbooks are opened. Therefore, if you record actions you want Excel to perform on the default Book1 workbook or a workbook loaded from the XLStart folder, the Auto_Open macro will fail when you restart Excel because it runs before the default and start-up workbooks are opened.

If you encounter these limitations, instead of recording an Auto_Open macro, you must create a VBA procedure for the Open event as described in the next section of this article.

ShowCreating a VBA procedure for the Open event of workbook

Another way to automatically run a macro when you open a workbook is to write a VBA procedure in the Open event of the workbook by using the Visual Basic Editor. The Open event is a built-in workbook event that runs its macro code each time you open the workbook.

The following simple example uses the Open event to run a macro when you open the workbook.

  1. Save and close all open workbooks.
  2. Open the workbook you want add the macro to, or create a new workbook.
  3. On the Tools menu, point to Macro, and then click Visual Basic Editor.
  4. In the Project Explorer window, right-click the ThisWorkbook object, and then click View Code on the shortcut menu.
  5. In the Object list above the Code window, select Workbook. This will automatically create an empty procedure for the Open event like this:
Private Sub Workbook_Open()

End Sub
  1. Add the following lines of code to the procedure:
Private Sub Workbook_Open()
    MsgBox Date
    Worksheets("Sheet1").Range("A1").Value = Date
End Sub
  1. Switch to Microsoft Excel and save the workbook.
  2. Close and reopen the workbook. When you open the file again, Excel runs the Workbook_Open procedure, which displays today's date in a message box.
  3. Click OK in the message box.
  4. Note that cell A1 on Sheet1 also contains the date as a result of the Workbook_Open procedure.

It is beyond the scope of this article to provide detailed information on programming Excel. For more information about working with Excel using VBA code, see Working with Excel Objects in the Microsoft Office 2000/Visual Basic Programmer's Guide. For additional information on programming Office applications, see the Microsoft Office Developer Center.