Page 204 - Excel Data Analysis

10 537547 Ch09.qxd 3/4/03 12:13 PM Page 190

EXCEL DATA ANALYSIS

RUN A MACRO AS A WORKBOOK OPENS

f you repeatedly perform the same tasks or execute the in the ThisWorkbook object code module, it can access
same macro whenever you open a workbook, you may other procedures within the same workbook. Therefore,
I want to have the macro execute automatically. You can you can create a Workbook_Open procedure that calls
create a macro that runs automatically each time a procedures located in another module.
particular workbook opens. Because this type of macro If you have a procedure that you want to execute whenever
executes only once as the workbook opens, it works well Excel opens, you must place the procedure within the
for launching custom menus and toolbars, opening other ThisWorkbook object for the Personal Macro Workbook,
workbooks, determining whether specific conditions are Personal.xls. Because the Personal Macro Workbook
met, or displaying welcome messages. The macro executes always loads as a hidden workbook in Excel, any procedures
when the workbook opens by catching the Open event that within this workbook appear to execute as Excel opens.
the opening workbook triggers.
Keep in mind, however, that Excel associates the Personal
To create a macro that executes when a workbook opens, Macro Workbook with an individual user.
you need to create a new procedure named You can keep a Workbook_Open procedure from executing
Workbook_Open and add it to the ThisWorkbook object for a particular workbook by holding down the Shift key as
code module for the workbook. In fact, all event-handling the workbook opens. Because workbooks typically open
procedures that you create for monitoring workbook events rather quickly, make sure that you press and hold the Shift
must reside within the ThisWorkbook object to have Excel key as soon as you select the workbook.
execute them automatically. Although the procedure resides
RUN A MACRO AS A WORKBOOK OPENS

Workbook

ThisWorkbook

⁄ In the Project Explorer, ¤ Double-click the ‹ In the Object box, click
locate the workbook to ThisWorkbook object the dropdown arrow and
which you want to add node under the workbook. select the Workbook option.
the Workbook_Open
subroutine. ■ The code module opens
for the ThisWorkbook
object.

190