To define an automatic procedureA workbook-level automatic procedure is stored in a workbook and runs automatically whenever an event such as opening or closing a workbook occurs. You can place automatic procedures in any Visual Basic module in the workbook.
Workbook-level automatic procedures are identified by their names as they appear in a module — Auto_Open or Auto_Close, for example. When you name the procedure in a module, the name isn't case sensitive; therefore, Auto_Open and auto_open are equivalent.
The following table lists the automatic procedure names available in Microsoft Excel and describes the event that causes each procedure to run.
|
Procedure name |
Event that causes the procedure to run | |
|
Auto_Open |
User opens the workbook that contains the procedure. | |
|
Auto_Close |
User closes the workbook that contains the procedure. | |
|
Auto_Add |
User installs the add-in that contains the procedure, or the Installed property of the add-in is set to True. For more information, see Chapter 12, "Creating Add-Ins." | |
|
Auto_Remove |
User removes the add-in that contains the procedure, or the Installed property of the add-in is set to False. For more information, see Chapter 12, "Creating Add-Ins." | |
Note
To give you more programming flexibility, the Auto_Open and Auto_Close procedures don't run if you open or close a workbook under program control. For example, the statement Workbooks.Open filename:="book2.xls" opens the workbook but doesn't run the Auto_Open procedure. To run the Auto_Open procedure under program control, use the RunAutoMacros method. For more information, see "RunAutoMacros method" in Help.
To define an automatic procedure
1. Switch to the Visual Basic module where you want to store the automatic procedure.
2. Create a new procedure named "Auto_Open," "Auto_Close," "Auto_Add," or "Auto_Remove."
3. Write the code for the procedure.
To test an automatic procedure without having to explicitly open or close the workbook, click Macro on the Tools menu, click the name of the procedure, and then click Run.
You can use at most one Auto_Open and one Auto_Close procedure in each workbook. If more than one Auto_Open or Auto_Close procedure exists in a workbook, none of them will run when the user opens or closes the workbook.
If more than one Auto_Add or Auto_Remove procedure exists in an add-in, an error occurs when the user installs or removes the add-in.
Note
You can prevent an automatic procedure from running by holding down Shift while opening or closing a workbook or switching to or from a worksheet. For example, you can hold down Shift while opening an application that's under development to avoid running any of its Auto_Open procedures.