Run A Procedure When Excel Creates A Workbook

You can use the NewWorkbook application event to create a procedure that executes whenever Excel opens a new workbook. When you use an application event, you capture the events that the application — in this case, Excel — creates. The NewWorkbook event triggers whenever Excel creates a new workbook. Because the event comes from the application and not an individual object such as a workbook, or chart, you may find the process a little more complex than capturing other object events.

When working with application events, you first create a class module. Excel only makes code within a code module available to other modules within the same project or workbook. Because you create a procedure dealing with an application event, you want all open projects to access the code;therefore, you need to use a class module.

Because you must activate the event-handling code for an application object, you need to place the code module you create in a commonly used workbook. Because Excel does not recognize your application event code until the workbook containing the code opens, consider adding the code to the Personal.xls workbook. Because that workbook opens whenever you run Excel, the application event code activates as the workbook opens. See Chapter 1 for more information about the Personal Macro workbook.

Within the class module you define an event custom object using the WithEvents keyword. The WithEvents keyword instructs Excel to notify you whenever the Application object triggers a NewWorkbook event. You use the Public statement because you want all open projects to access this object variable. See Chapter 5 for more information on using the Public keyword.

RUN A PROCEDURE WHEN EXCEL CREATES A WORKBOOK

RUN A PROCEDURE WHEN EXCEL CREATES A WORKBOOK

Window Vba Class

'-n In the Project window, click to highlight a workbook you open frequently.

■ You must open this workbook to activate the event code.

■ Excel creates a blank class module.

□ Type Public WithEvents AppEvent As Application, replacing AppEvent with the name of the application event object.

'-n In the Project window, click to highlight a workbook you open frequently.

■ You must open this workbook to activate the event code.

■ Excel creates a blank class module.

Type a name for the code module in the (Name) field of the Properties window.

□ Type Public WithEvents AppEvent As Application, replacing AppEvent with the name of the application event object.

AUTOMATING PROCEDURES WITH EXCEL EVENTS

When you specify the public Application object using the WithEvents keyword, the Visual Basic Editor creates a new object and adds it to the Object drop-down list. When you select this object, the Procedure box contains a list of all corresponding application events. To create a new event procedure, you select the object from the Object drop-down list and the appropriate event from the Procedure drop-down list. When you do this, the Visual Basic Editor creates the new subroutine with the appropriate arguments. For example, if your object is AppEvent and you select the WindowActivate event, the Editor adds the following code to the class module:

Example:

Private Sub AppEvent_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)

End Sub

You can use the Object Browser to find out more about a particular event by pressing F2. Type the event you want to know about and click the Search icon (HI). The Object Browser displays a list of the matching items. Excel indicates the Events with a small lightning bolt icon (|T|). If you click an event, the event syntax displays at the bottom of the Object Browser window.

-■ The class module name changes to the name you specify in step 3.

0 In the Object list, click □ and then the option you named in step 3.

-■ The class module name changes to the name you specify in step 3.

0 In the Object list, click □ and then the option you named in step 3.

□ In the Projects window, double-click the ThisWorkbook object for the open workbook.

■ Excel creates a Private AppEvent_NewWorkbook subroutine where AppEvent is the name of the application object you created in step 4.

L0 Type the VBA code to execute when a new workbook opens.

□ In the Projects window, double-click the ThisWorkbook object for the open workbook.

CONTINUED

0 0

Post a comment