Excel 2019 Bible

Chapter 46: Working with Excel Events

46

A problem can arise from this event with unsaved workbooks. By the time the user sees the prompt to save before
closing, the BeforeClose event has already executed. If the user cancels the close at the save prompt, the work-
book remains open, but your event code has already run.

Working with Worksheet Events

The events for a Worksheet object are some of the most useful. As you’ll see, monitoring

these events can make your applications perform feats that would otherwise be impossible.

Table 46.2 lists the more commonly used worksheet events with a brief description of each.

Remember that these event procedures must be entered into the code module for the sheet.

These code modules have default names like Sheet1, Sheet2, and so on.

TABLE 46.2 Worksheet Events

Event Action That Triggers the Event

Activate The worksheet is activated.

BeforeDoubleClick The worksheet is double-clicked. This event occurs before the

default double-click action.

BeforeRightClick The worksheet is right-clicked. This event occurs before the

default right-click action.

Change Cells on the worksheet are changed by the user.

Deactivate The worksheet is deactivated.

FollowHyperlink A hyperlink on the worksheet was clicked.

SelectionChange The selection on the worksheet is changed.

Using the Change event

A Change event is triggered when a user changes any cell in the worksheet. A Change

event is not triggered when a calculation generates a different value for a formula or when

an object (such as a chart or a shape) is added to the sheet.

When the Worksheet_Change procedure executes, it receives a Range object as its

Target argument. This Range object corresponds to the changed cell or range that trig-

gered the event. The following example displays a message box that shows the address of

the Target range:

Private Sub Worksheet_Change(ByVal Target As Range)

MsgBox "Range " & Target.Address & " was changed."

End Sub