Page 302 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

SelectionChange Event
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 win­
dow until the current selection is in the upper-left corner of the window.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveWindow
ScrollRow = Target.Row
ScrollColumn = Target.Column
End With
End Sub

BeforeRightClick Event

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.

Note 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 _
As Boolean)
Cancel = True
MsgBox "The shortcut menu is unavailable for " & Cells.Worksheet.Name
End Sub

Application Events

The events for the Application occur when any workbook is created, opened, or changed. If
you need to write an event procedure at the Application level, you need to create a new object
in a class module. After the new class module is created, you can attach macros with a variety
of events, such as NewWorkbook, SheetActivate, or WorkbookOpen.

Chapter 12

276
Part 4: Advanced VBA