Using Worksheet-Level Automatic Procedures

A worksheet-level automatic procedure is associated with a specific worksheet and runs automatically when an event such as activating or deactivating a worksheet occurs. In most cases, it's better to use workbook-level automatic procedures than worksheet-level procedures. This is because when you use worksheet-level procedures, you must explicitly define procedures for each worksheet you want to support, and because these worksheet-level procedures are then not available for chart sheets and Visual Basic modules.

Worksheet-level automatic procedures are associated with defined names that begin with Auto_Open_, Auto_Close_, Auto_Activate_, or Auto_Deactivate_. These defined names aren't case sensitive; therefore, Auto_Open_ and auto_open_ are equivalent. The following table lists the automatic procedure names available in Microsoft Excel, and it describes the event that causes each procedure to run.

To create a defined-name automatic procedure

1. Switch to the worksheet for which you want to define an automatic procedure.

2. On the Insert menu, point to Name, and then click Define.

3. In the Names In Workbook box, type a name that begins with Auto_Open_, Auto_Close_, Auto_Activate_, or Auto_Deactivate_. Precede the name with the worksheet name, followed by an exclamation point — for example, type Sheet1!Auto_Open_First

4. In the Refers To box, type an equal sign and the name of the procedure you want to associate with the defined name; for example, =CheckData would be a valid entry in the Refers To box.

The procedure named in the Refers To box runs when the workbook that contains the worksheet is opened or closed or when the worksheet is activated or deactivated. You can use the same name you typed in the Names In Workbook box, or you can type a different name.

5. Click Add.

Note

If the procedure you want to associate with the defined name is in a different workbook, you must specify the name of the workbook, followed by an exclamation point, along with the procedure name. For example, if the CheckData procedure were in another workbook named "DataBook," you would type =DataBook!CheckData in the Refers To box.

If the procedure you want to associate with the defined name has the same name as another procedure in the same workbook, you must specify the name of the module, followed by a period, along with the procedure name. For example, to specify the CheckData procedure in the module called "DataLib" in the workbook that contains the defined name, you would type =DataLib.CheckData in the Refers To box.

To specify one of several procedures with the same name in a workbook other than the one that contains the defined name, specify the workbook name and the module name in the Refers To box; for example, type =DataBook!DataLib.CheckData.

Defined-name automatic procedures differ from standard automatic procedures in that the name defined on the worksheet needs only to begin with the name of the type of the procedure you want to run for the event. For example, you can define the names Auto_Open_Public, Auto_Open_StartupScreen, and Auto_Open_Files on the same worksheet; when the user opens the workbook containing the worksheet, the procedures associated with all three names run.

Standard automatic procedures run before defined-name procedures. For example, suppose that you place an Auto_Open procedure in a module, and on a certain worksheet you define the name Auto_Open_ThisWorksheet as the text StartupWorksheet. When the user opens the workbook, the workbook-level Auto_Open procedure runs before the StartupWorksheet procedure. Note that you cannot set the order in which the Auto_Open procedures defined in a worksheet will run.

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.