From Excel Help
Have a macro run automatically when you open a workbook
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. 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.
Create an Auto_Open macro- If you want to save the macro with a particular workbook, open that workbook first.
- On the Tools menu, point to Macro, and then click Record New Macro.
- In the Macro name box, type Auto_Open.
- 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 that 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. - Click OK, and then perform the actions that you want to record.
- On the Stop Recording toolbar, click the Stop Recording button.
- 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
- 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 that you want Excel to perform on the default Book1 workbook or on 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.
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 [bbcode][/bbcode] method.
- An Auto_Open macro runs before any other workbooks are opened. Therefore, if you record actions that you want Excel to perform on the default Book1 workbook or on 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 [bbcode][/bbcode] event as described in the next section of this article.
Note: the apparent links above are not working links, they work within Excel Help.