Excel 2010 Bible

Chapter 43: Working with Excel Events

883

Caution

You won’t want to use this procedure if your worksheet contains background shading because the macro will

erase it. However, if the shading is the result of a style applied to a table, the macro does not erase the table’s

background shading. n

Using the BeforeRightClick event

Normally, when the user right-clicks in a worksheet, a shortcut menu appears. If, for some reason,

you want to prevent the shortcut menu from appearing, you can trap the RightClick event. The

following procedure sets the Cancel argument to TRUE, which cancels the RightClick event —

and, thus, the shortcut menu. Instead, a message box appears.

Private Sub Worksheet_BeforeRightClick _

(ByVal Target As Excel.Range, Cancel As Boolean)

Cancel = True

MsgBox “The shortcut menu is not available.”

End Sub

Using Non-Object Events

So far, the events discussed in this chapter are associated with an object (Application,

Workbook, Sheet, and so on). This section discusses two additional events: OnTime and OnKey.

These events are not associated with an object. Rather, you access them by using methods of the

Application object.

Note
Unlike the other events discussed in this chapter, you use a general VBA module to program the On events in
this section. n

Using the OnTime event

The OnTime event occurs at a specified time. The following example demonstrates how to pro-

gram Excel to beep and then display a message at 3 p.m.:

Sub SetAlarm()

Application.OnTime 0.625, “DisplayAlarm”

End Sub

Sub DisplayAlarm()

Beep

MsgBox “Wake up. It’s time for your afternoon break!”

End Sub