Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

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.

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"

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.

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

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.

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

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}", ""

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.

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.