Chapter 43: Working with Excel Events

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 program 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

883

Part VI: Programming Excel with VBA

In this example, the SetAlarm procedure uses the OnTime method of the Application object to set up the OnTime event. This method takes two arguments: the time (0.625, or 3 p.m., in the example) and the procedure to execute when the time occurs (DisplayAlarm in the example).

In the example, after SetAlarm executes, the DisplayAlarm procedure is called at 3 p.m., bringing up the message.

Most people find it difficult to think of time in terms of Excel’s time numbering system. Therefore, you may want to use the VBA TimeValue function to represent the time. TimeValue converts a string that looks like a time into a value that Excel can handle. The following statement shows an easier way to program an event for 3 p.m.:

Application.OnTime TimeValue(“3:00:00 pm”), “DisplayAlarm”

If you want to schedule an event that’s relative to the current time — for example, 20 minutes from now — you can write an instruction like this:

Application.OnTime Now + TimeValue(“00:20:00”), “DisplayAlarm”

You also can use the OnTime method to schedule a procedure on a particular day. Of course, you must keep your computer turned on, and Excel must be running.

Using the OnKey event

While you work, Excel constantly monitors what you type. As a result, you can set up a keystroke or a key combination that — when pressed — executes a particular procedure.

The following example uses the OnKey method to set up an OnKey event. This event essentially reassigns the PgDn and PgUp keys. After the Setup_OnKey procedure executes, pressing PgDn executes the PgDn_Sub procedure, and pressing PgUp executes the PgUp_Sub procedure. The next effect is that pressing PgDn moves down one row, and pressing PgUp moves up one row.

Sub Setup_OnKey()

Application.OnKey “{PgDn}”, “PgDn_Sub”

Application.OnKey “{PgUp}”, “PgUp_Sub”

End Sub

Sub PgDn_Sub()

On Error Resume Next

ActiveCell.Offset(1, 0).Activate

End Sub

Sub PgUp_Sub()

On Error Resume Next

ActiveCell.Offset(-1, 0).Activate

End Sub

884

Chapter 43: Working with Excel Events

Note

The key codes are enclosed in brackets, not parentheses. For a complete list of the keyboard codes, consult VBA Help. Search for OnKey. n

Tip

The preceding examples used On Error Resume Next to ignore any errors generated. For example, if the active cell is in the first row, trying to move up one row causes an error. Furthermore, if the active sheet is a chart sheet, an error occurs because no such thing as an active cell exists in a chart sheet.

By executing the following procedure, you cancel the OnKey events, and the keys return to their normal functions.

Sub Cancel_OnKey()

Application.OnKey “{PgDn}”

Application.OnKey “{PgUp}”

End Sub

Caution

Contrary to what you may expect, using an empty string as the second argument for the OnKey method does not cancel the OnKey event. Rather, it causes Excel to ignore the keystroke and do nothing. For example, the following instruction tells Excel to ignore Alt+F4 (the percent sign represents the Alt key):

Application.OnKey “%{F4}”, “”

885