|< Day Day Up >|| |
The events for the Worksheet object are some of the most useful events at your disposal in the world of Microsoft Excel. As you will see, the events for the Worksheet object occur when the worksheet is activated or the user changes the content of a cell. The Worksheet events are also activated by default, but as mentioned in the “Enabling and Disabling Events” section earlier, you can disable or deactivate the events.
To display the event procedures for a worksheet, use the Visual Basic Editor. Expand the desired project in the project window and double-click the worksheet to activate it. All event procedures associated with the worksheet will be displayed in the code window on the right side of the window.
To create a new Worksheet event procedure, follow these steps:
Double-click the Worksheet object named Sheet1 (Sheet1) to activate it.
Click the Object drop-down list, and select the Worksheet option.
Click the Procedure drop-down list, and select the desired event.
In Figure 12-3, you can see how to create a new Worksheet event procedure.
Figure 12-3: This is how to create a new Worksheet event procedure.
After you have created the new event procedure, you’ll notice the Private Sub Worksheet_Event and End Sub code has been added to the code window. Table 12-2 describes the commonly used Worksheet events.
Action That Triggers Event
Monitors the Change event for the worksheet. The event is triggered when the cells of the worksheet are changed by the user or by an external link.
Monitors the SelectionChange event for the worksheet. The event is triggered when the user chooses a new selection on the worksheet.
Monitors the BeforeRightClick event for the worksheet. The event is triggered when the user right-clicks the worksheet.
To navigate to the event procedures in a worksheet, you can right-click the sheet tab in the Excel program window and select View Code.
The Change event occurs when any cell in a worksheet is changed by the user, by VBA code, or by an external link. The Change event is not triggered when a calculation generates a different value for a formula, or when an object is added to the worksheet.
The Change event does not occur when cells change during recalculation. Use the Calculate event to trap a sheet recalculation.
When the Worksheet_Change procedure is executed, it receives a Range object as its Target argument. This Range object represents the changed cell or range that triggered the event. The following event procedure displays the address of the Target range:
Private Sub Worksheet_Change (ByVal Target As Excel.Range)
MsgBox "Range " & Target.Address & " was changed."
To get a better grasp of what causes the Change event to trigger, type the previous procedure into a code module and start modifying your worksheet. Every time the Change event occurs, you will see the address of the range that was modified.
There are some quirks associated with the Change event that you should be aware of, such as actions that should trigger the Change event but don’t, as well as actions that do trigger the Change event when they should not. The following list highlights some of these quirks:
Changing the format of the cell does not trigger the Change event, but using the Clear Formats command from the Edit menu does trigger the event.
Inserting, editing, or deleting a cell comment does not trigger the Change event.
Pressing the Delete or Backspace key and then pressing the Enter key triggers the Change event, even if the cell is empty.
Cells that are changed by using Excel commands might or might not trigger a change event. For example, adding new records to a Data Form or sorting data does not trigger the Change event. However, if you have made any spelling changes in your worksheet, using the Excel Spell checking feature or using the Replace feature will generate a Change event.
As you can see by the inconsistencies with the preceding list, it isn’t a good idea to rely on the Change event to detect all cell changes. However, you can work around these problems if you are aware of them. For example, if you know that the cell contents are required to have a specific format or value, you can use the BeforeSave event procedure to verify that the Change event did not miss an invalid entry.
The SelectionChange event procedure is executed each time the user selects a new cell or range of cells on the worksheet. This procedure is commonly used to assist the user in navigating through large files. For example, the event procedure could shade the row and column of the intersecting cell that is active. When a new selection is made you can turn off the current shading and then reapply the shading for the new row and column intersection.
The following event procedure was created to help the employees at The Garden Company navigate through the Productlist.xls file. The procedure scrolls through the workbook window until the current selection is in the upper-left corner of the window.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ScrollRow = Target.Row
ScrollColumn = Target.Column
The BeforeRightClick event is triggered when the user right-clicks the worksheet. If you would like to disable the shortcut menu from being displayed when the user right-clicks the worksheet, you can trap the RightClick event and set the Cancel argument to True.
The BeforeRightClick event does not occur if the pointer is on a shape, a toolbar, or a menu bar.
The following procedure will prevent the user from accessing the shortcut menus in a worksheet:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel _
Cancel = True
MsgBox "The shortcut menu is unavailable for " & Cells.Worksheet.Name
|< Day Day Up >|| |