Microsoft Office Tutorials and References
In Depth Information
Checking Out Chart Events
The BeforeRightClick event
When the user right-clicks in a worksheet, Excel displays a shortcut menu. If, for some reason,
you’d like to prevent the shortcut menu from appearing in a particular sheet, you can trap the
RightClick event. The following procedure sets the Cancel argument to True , which cancels
the RightClick event and thereby cancels the shortcut menu. Instead, a message box is
Private Sub Worksheet_BeforeRightClick _
(ByVal Target As Excel.Range, Cancel As Boolean)
Cancel = True
MsgBox “The shortcut menu is not available.”
End Sub
Keep in mind that the user can still access the shortcut menu by using Shift+F10. However, only a
tiny percentage of Excel users are aware of that keystroke combination.
To find out how to intercept the Shift+F10 key combination, see “The OnKey event,”
later in this chapter. Chapter 23 describes other methods for disabling shortcut menus.
Following is another example that uses the BeforeRightClick event. This procedure checks
to see whether the cell that was right-clicked contains a numeric value. If so, the code displays
the Number tab of the Format Cells dialog box and sets the Cancel argument to True
(avoiding the normal shortcut menu display). If the cell doesn’t contain a numeric value, nothing special
happens — the shortcut menu is displayed as usual.
Private Sub Worksheet_BeforeRightClick _
(ByVal Target As Excel.Range, Cancel As Boolean)
If IsNumeric(Target) And Not IsEmpty(Target) Then
Application.CommandBars.ExecuteMso (“NumberFormatsDialog”)
Cancel = True
End If
End Sub
Notice that the code makes an additional check to determine if the cell is not empty. This check is
because VBA considers empty cells to be numeric.
Checking Out Chart Events
This section describes some of the events associated with charts. By default, events are enabled
only for charts that reside on a chart sheet. To work with events for an embedded chart, you
need to create a class module.
Search JabSto ::

Custom Search