Events Not Associated with an Object

The events that I discuss earlier in this chapter are all associated with an object ( Application , Workbook , Sheet , and so on). In this section, I discuss two additional rogue events: OnTime and OnKey . These events are not associated with an object. Rather, they are accessed by using methods of the Application object.

Note  

Unlike the other events discussed in this chapter, you program these On events in a general VBA module.

The OnTime event

The OnTime event occurs at a specified time of day. The following example demonstrates how to program Excel so that it beeps and then displays a message at 3 p.m.:

 Sub SetAlarm()     Application.OnTime TimeValue("15:00:00"), "DisplayAlarm" End Sub Sub DisplayAlarm()     Beep     MsgBox "Wake up. It's time for your afternoon break!" End Sub 

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 (3 p.m., in the example) and the procedure to execute when the time occurs ( DisplayAlarm in the example). After SetAlarm is executed, the DisplayAlarm procedure will be called at 3 p.m., bringing up the message in Figure 19-10.

Figure 19-10: This message box was programmed to display at a particular time of day.

If you want to schedule an event 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 can also use the OnTime method to schedule a procedure on a particular day. The following statement runs the DisplayAlarm procedure at 12:01 a.m. on April 1, 2008:

 Application.OnTime DateSerial(2008, 4, 1) + _     TimeValue("00:00:01"), "DisplayAlarm" 

Note  

The OnTime method has two additional arguments. If you plan to use this method, you should refer to the online help for complete details.

The two procedures that follow demonstrate how to program a repeated event. In this case, cell A1 is updated with the current time every five seconds. Executing the UpdateClock procedures writes the time to cell A1 and also programs another event five seconds later. This event re-runs the UpdateClock procedure. To stop the events, execute the StopClock procedure (which cancels the event). Note that NextTick is a module-level variable that stores the time for the next event.

CD-ROM  

This example, named  ontime event demo.xlsm , is available on the companion CD-ROM.

 Dim NextTick As Date Sub UpdateClock() '   Updates cell A1 with the current time     ThisWorkbook.Sheets(1).Range("A1") = Time '   Set up the next event five seconds from now     NextTick = Now + TimeValue("00:00:05")     Application.OnTime NextTick, "UpdateClock" End Sub Sub StopClock() '   Cancels the OnTime event (stops the clock)     On Error Resume Next     Application.OnTime NextTick, "UpdateClock", , False End Sub 

Caution  

The OnTime event persists even after the workbook is closed. In other words, if you close the workbook without running the StopClock procedure, the workbook will reopen itself in five seconds ( assuming that Excel is still running). To prevent this, use a Workbook_BeforeClose event procedure that contains the following statement:

 Call StopClock 

CROSS-REFERENCE  

To see an example of a repeating OnTime event, see the analog clock example in Chapter 18.

The OnKey event

While you're working, Excel constantly monitors what you type. Because of this, you can set up a keystroke or a key combination that, when pressed, executes a particular procedure. The only time these keystrokes won't be recognized is when you're entering a formula or working with a dialog box.

Caution  

It's important to understand that creating a procedure to respond to an OnKey event is not limited to a single workbook. The re-mapped keystroke is valid in all open workbooks, not just the one in which you created the event procedure.

Also, if you set up an OnKey event, make sure that you provide a way to cancel the event. A common way to do this is to use the Workbook_BeforeClose event procedure.

AN ONKEY EVENT EXAMPLE

The following example uses the OnKey method to set up an OnKey event. This event reassigns the PgDn and PgUp keys. After the Setup_OnKey procedure is executed, pressing PgDn executes the PgDn_Sub procedure, and pressing PgUp executes the PgUp_Sub procedure. The net effect is that pressing PgDn moves the cursor down one row, and pressing PgUp moves the cursor 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 

CD-ROM  

This example, named  onkey event demo.xlsm , is available on the companion CD-ROM.

In the preceding examples, I use On Error Resume Next to ignore any errors that are generated. For example, if the active cell is in the first row, trying to move up one row causes an error. Also, if the active sheet is a chart sheet, an error will occur because there is no such thing as an active cell in a chart sheet.

By executing the following procedure, you cancel the OnKey events and return these keys to their normal functionality:

 Sub Cancel_OnKey()     Application.OnKey "{PgDn}"     Application.OnKey "{PgUp}" End Sub 

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

 Application.OnKey "%{F4}", "" 

CROSS-REFERENCE  

Although you can use the OnKey method to assign a shortcut key for executing a macro, it's better to use the Macro Options dialog box for this task. For more details, see Chapter 9.

KEY CODES

In the previous section, notice that the PgDn keystroke appears in braces. Table 19-6 shows the key codes that you can use in your OnKey procedures.

Table 19-6: KEY CODES FOR THE ONKEY EVENT
Open table as spreadsheet

Key	Code
Backspace	{BACKSPACE} or {BS}
Break	{BREAK}
Caps Lock	{CAPSLOCK}
Delete or Del	{DELETE} or {DEL}
Down Arrow	{DOWN}
End	{END}
Enter	(tilde)
Enter (on the numeric keypad)	{ENTER}
Escape	{ESCAPE} or {ESC}
Home	{HOME}
Ins	{INSERT}
Left Arrow	{LEFT}
NumLock	{NUMLOCK}
Page Down	{PGDN}
Page Up	{PGUP}
Right Arrow	{RIGHT}
Scroll Lock	{SCROLLLOCK}
Tab	{TAB}
Up Arrow	{UP}
F1 through F15	{F1} through {F15}

You can also specify keys combined with Shift, Ctrl, and Alt. To specify a key combined with another key or keys, use the following symbols:

• Shift: Plus sign (+)

• Ctrl: Caret ( ˆ˜ )

• Alt: Percent sign (%)

For example, to assign a procedure to the Ctrl+Shift+A key, use this code:

 Application.OnKey "^+A", "SubName" 

To assign a procedure to Alt+F11 (which is normally used to switch to the VB Editor window), use this code:

 Application.OnKey "^{F11}", "SubName" 

DISABLING SHORTCUT MENUS

Earlier in this chapter, I discuss a Worksheet_BeforeRightClick procedure that disables the right-click shortcut menu. The following procedure is placed in the ThisWorkbook code module:

 Private Sub Worksheet_BeforeRightClick _   (ByVal Target As Excel.Range, Cancel As Boolean)     Cancel = True     MsgBox "The shortcut menu is not available." End Sub 

I also noted that the user could still display the shortcut menu by pressing Shift+F10. To intercept the Shift+F10 key combination, add these procedures to a standard VBA module:

 Sub SetupNoShiftF10()     Application.OnKey "+{F10}", "NoShiftF10" End Sub Sub TurnOffNoShiftF10()     Application.OnKey "+{F10}" End Sub Sub NoShiftF10()     MsgBox "Nice try, but that doesn't work either." End Sub 

After the SetupNoShiftF10 procedure is executed, pressing Shift+F10 displays the message box shown in Figure 19-11. Remember that the Worksheet_BeforeRightClick procedure is valid only in its own workbook. The Shift+F10 key event, on the other hand, applies to all open workbooks.

Figure 19-11: Pressing Shift+F10 displays this message.

Note  

Some keyboards have a dedicated key that displays a shortcut menu. On my keyboard, that key is on the right side of the keyboard between the Windows key and the Ctrl key. I was surprised to discover that intercepting the Shit+F10 key combination also disables the dedicated shortcut menu key.

CD-ROM  

The companion CD-ROM contains a workbook that includes all of these procedures. The file, named  no shortcut menus.xlsm , includes workbook event handler procedures: Workbook_Open executes the SetupNoShiftF10 procedure, and Workbook_BeforeClose calls the TurnOffNoShiftF10 procedure.