Part VI: Automating Excel
The TimeToRun variable can be used to unschedule it, assuming that the variable is still in
scope:
Application.OnTime TimeToRun, "DisplayAlarm", , False
Using the OnKey event
While you work, Excel constantly monitors what you type. As a result, you can set up a key-
stroke 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 essen-
tially 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 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
The key codes are enclosed in brackets, not parentheses. For a complete list of the keyboard codes, consult VBA
Help. Search for OnKey.
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.
