OnTime and OnKey Events

‘OnTime’ event allows you to execute a code at a specific time or after a specific time has elapsed. ‘OnKey’ event allows you to execute a code when a specific keystroke (or a combination of keystrokes) is used.

OnTime Events

This can enable you to fire off a piece of code at regular intervals for as long as the workbook is loaded into Excel. You may want to auto-save your workbook to a different folder every 10 minutes, or leave the worksheet running overnight so as to bring in the latest data from an external source.

In this example, a sub routine is entered into a module. It displays a message box to every 5 minutes, although this could easily be another coded procedure. At the same time, it resets the timer to the current time plus 5 more minutes.

Example 1,

Every time it runs, the timer resets to run the same sub routine in another 5 minutes time.

Sub TestOnTime()
MsgBox \”Testing OnTime\”
Application.OnTime (Now () + TimeValue(\”00:05:00\”)), \”TestOnTime\”
End Sub

Application.OnKey Events

This function enables you to design your own hot keys.  You can make any key combination call a sub routine of your creation.

Example 1,

In this example the letter ‘a’ is redirected so that instead of placing an ‘a’ in a cell, it will display a message box.  This code needs to be placed in an inserted module.

Sub TestKeyPress()
Application.OnKey \”a\”, \”TestKeyPress\”
End Sub

Sub TestKeyPress()
MsgBox \”You pressed \’a\’\”
End Sub

You run the sub routine ‘TestKeyPress’ first of all.  You only need to run this once. It tells Excel that every time the letter ‘a’ is pressed it will call the sub routine ‘TestKeyPress’.  The sub routine ‘TestKeyPress’ just displays a message box to tell you that you pressed key ‘a’.  It could of course load a form or do all sorts of other things.

Example 2,

To cancel this functionality, you run the ‘OnKey’ statement without the ‘Procedure’ parameter.

Sub CancelOnKey()
Application.OnKey \”a\”
End Sub