Run A Procedure When Excel Creates A Workbook

You use the NewWorkbook event to determine when Excel has created a new workbook. The NewWorkbook event has one parameter value that passes into the subroutine. The Wb parameter contains the new created workbook. You can access any of the methods and properties of the new workbook to customize the created workbook. For example, you can use the Name property to return the name of the new workbook. See Chapter 9 for more information on working with the Workbook object.

Creating the NewWorkbook subroutine in the class module simply defines the code to run for the event, but does not activate the code. To activate the subroutine, add code to a Workbook_Open procedure that activates the Application event procedure. Because the

Application event code is meant to work with all events generated by the application, you want to add the class module and the activation code to a workbook you open frequently, such as the Personal Macro workbook.

To activate the class module code, the module containing the activation procedure must contain a Dim statement, which declares an object of the type defined in the class module. You must place the Dim statement at the top of the code module. For example, Dim NewAppEvent As New AppEvent creates a new object variable of the type created in the class module. Within a procedure, you add a Set statement which actually activates the event. To make the Set statement execute automatically, you place the Set statement within the Workbook_Open procedure.

RUN A PROCEDURE WHEN EXCEL CREATES A WORKBOOK (CONTINUED)

RUN A PROCEDURE WHEN EXCEL CREATES A WORKBOOK (CONTINUED)

■ The code module opens for the ThisWorkbook object.

° Type Dim Test As New AppEvent at the top of the code module, replacing Test with the local object created in step 4 and AppEvent with the object module created in step 3.

_• In the Workbook_Open subroutine, type Set Test.AppEvent = Excel.Application, replacing Test with the variable in step 7 and AppEvent with the variable created in step 3.

Note: If the Workbook_Open subroutine does not exist, see the section "Run a Procedure as a Workbook Opens" for information on creating one.

, Close and reopen Excel.

AUTOMATING PROCEDURES WITH EXCEL EVENTS

When you open the workbook containing the code that activates the application event, the code continues to execute each time you trigger the event. You may find circumstances where you need to deactivate an event so that it no longer triggers. To do so, you can create a separate subroutine that you can call from within Excel at any point to cancel an event. Essentially, you set the property of the application object to Nothing, as shown in the code:

TYPE THIS:

RESULT:

The code cancels the event for the current session of Excel. The next time you execute Excel, the event is activated again.

Keep in mind that you must use the same object and property references that you used to create the application object. It is a good idea to create this type of subroutine to enable you to disable an event-handling procedure at any time. Another method you can use is to set the EnableEvents property to false for the Application object, as shown in this code:

TYPE THIS:

RESULT:

This code disables all event-handling procedures for the current session of Excel. The next time you execute Excel, the event-handling procedures are reactivated.

— Open the workbook containing the Workbook_Open subroutine referenced in step 8.

■ The event-handling procedure executes the specified VBA code as a new workbook is opened.

— Open the workbook containing the Workbook_Open subroutine referenced in step 8.

■ The event-handling procedure executes the specified VBA code as a new workbook is opened.

MONITOR A RANGE OF CELLS FOR CHANGES

You can create a procedure that watches a particular cell or ranges of cells until a change occurs. To monitor a range of cells you capture the Change event that triggers for the Worksheet object. Excel triggers this event when the user or an external link changes the values within the selected worksheet. When Excel triggers the event, it sends your event-handling function a Range object containing the cells that changed. You design your procedure to check the range of cells and determine if they are within the range of cells you are monitoring.

Because the event you monitor relates to an individual worksheet, you place the event-handling procedure within the object module code that corresponds to the appropriate worksheet. For example, to monitor the changes to Sheetl, you place the code in the code module for Sheetl. To capture the Change event, you must name the procedure you create Worksheet_Change.

The Change event has one parameter, Target, whose value Excel passes when it triggers the Change event. The Target parameter receives the range of cells that were altered. This value passes to your procedure by value so that you receive a copy of the range of cells.

Although the Worksheet_Change procedure resides in a sheet object code module, it can access other procedures within the same workbook. Therefore, you can create a Worksheet_Change procedure that calls procedures located in another module.

Keep in mind, Excel only triggers this event when cell values change due to modifications made by the user or an external link. It does not trigger if a formula or procedure performs a calculation that changes the value, or if you add an object.

MONITOR A RANGE OF CELLS FOR CHANGES

MONITOR A RANGE OF CELLS FOR CHANGES

'-n In the Projects window, locate the worksheet where you want to add the Worksheet_Change subroutine.

In the Object box, click Q and then the Worksheet option.

□ In the Procedure box, click Q and then the Change option.

'-n In the Projects window, locate the worksheet where you want to add the Worksheet_Change subroutine.

B Double-click the sheet object code that corresponds to the appropriate worksheet.

■ The code module opens for the selected sheet object.

In the Object box, click Q and then the Worksheet option.

□ In the Procedure box, click Q and then the Change option.

AUTOMATING PROCEDURES WITH EXCEL EVENTS

Because Excel only triggers the Change event when a user or external link changes the values of cells, you may find instances where you expect Excel to trigger a change event, and it does not. The following table compares incidents where Excel triggers a Change event versus times it does not.

TRIGGER CHANGE EVENT

DOES NOT TRIGGER CHANGE EVENT

Type value in a cell

Calculate new value for formula

Edit O Clear Formats

Change cell formatting

Edit O Fill

Data O Form

Press Delete

Data O Sort

Edit O Delete

Change from a procedure (macro)

Tools O Spelling

Insert O Comment

Edit O Replace

Insert O Picture Insert O Diagram

You can use the Calculate event to determine when cell values change due to recalculating a worksheet. You create an event-handling procedure for this event in the same fashion as the Change event. The only real difference is that the Calculate event does not have any parameter values. The following code shows how to initiate a Calculate procedure:

Example:

Private Sub Worksheet_Calculate()

End Sub

I The Visual Basic

The Worksheet_Change

I The Visual Basic

Editor creates a new Private subroutine named Worksheet_Change.

-0 Type the VBA code to run when the workbook opens.

Note: See Chapter 11 for information on using the Intersect method to compare ranges.

□ Switch to Excel and click a cell.

The Worksheet_Change procedure executes the specified VBA code to determine if the selected cell is within the specified range.

0 -1

Post a comment