Expounding on Worksheet Events

The Worksheet object doesn't have very many events, but what it lacks in number, it makes up for in terms of usefulness. Table 7.12 lists the events associated with the Worksheet object.

Table 7.1: Events Associated with the worksheet Object

Event

Occurs when

Activate

The worksheet is activated.

BeforeDoubleClick

The worksheet is double-clicked. Occurs before the default double-click action.

BeforeRightClick

The worksheet is right-clicked. Occurs before the default right-click action.

Calculate

After the worksheet is recalculated.

Change

Cells on the worksheet are changed by the user or an external link.

Deactivate

The worksheet is deactivated.

FollowHyperlink

A hyperlink on a worksheet is clicked.

PivotTableUpdate

Occurs after a PivotTable report is updated on a worksheet.

SelectionChange

The selection changes on a worksheet.

If you compare these events to the events associated with the Workbook object, you see that all of these events also have a related event that occurs in the Workbook object. The difference is that the events in the Workbook object are not sheet specific whereas these are.

For example, if you only cared about changes made to a worksheet named Income Statement, you'd place the appropriate code in the Worksheet Change event associated with the Income Statement worksheet rather than the Sheet Change event associated with the Workbook. Although the Sheet Change event associated with the Workbook object would do the chore, it wouldn't be very efficient because it would also occur any time a change was made on any other worksheet. As a result, you'd always have to see which worksheet the change was being made to. In Figure 7.2, you can see an example of a Worksheet Change event.

Two events in particular pack a strong one-two wallop. The Worksheet Change event and the Worksheet SelectionChange event provide you with a tremendous amount of flexibility to do things like the following:

♦ Implement your own custom data validation routines.

♦ Automatically update reports, charts, or other items in a workbook based on an entry made on a worksheet.

♦ Display user notifications or forms in response to an entry or movement on a worksheet.

♦ Play practical jokes on your colleagues (OK, I admit that this is not a particularly good idea given all of the rightful paranoia going around about computer viruses, worms, and such).

Figure 7.2

Make sure you have the appropriate worksheet selected in the Project Explorer before you select the event to respond to.

Figure 7.2

Make sure you have the appropriate worksheet selected in the Project Explorer before you select the event to respond to.

Listing 7.10 shows an example of how you can use the Worksheet Change event to watch specific worksheet cells for changes and then react if the cell is changed to a value of interest.

0 0

Post a comment