Events Not Associated with Objects

The events that I discuss previously in this chapter are associated with either a workbook object or a worksheet object. In this section, I discuss two types of events that are not associated with objects: time and keypresses.

Because time and keypresses aren't associated with a particular object such as a workbook or a worksheet, you program these events in a normal VBA module (unlike the other events discussed in this chapter).

The OnTime event

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



Application.OnTime 0

.625, 1







MsgBox "Wake up. It'

s time

for your afternoon break!"



In this example, I use the OnTime method of the Application object. This method takes two arguments: the time (0.625 or 3:00 p.m.) and the name of the sub procedure to execute when the time occurs (DisplayAlarm).

This procedure is quite useful if you tend to get so wrapped up in your work that you forget about meetings and appointments. Just set an OnTime event to remind yourself.

Most people (this author included) find it difficult to think of time in terms of the Excel 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:00 p.m.:

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

If you want to schedule an event relative to the current time — for example, 20 minutes from now — you can use a statement like this:

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

You can also use the OnTime method to run a VBA procedure on a particular day. You must make sure that your computer keeps running and that the workbook with the procedure is kept open. The following statement runs the DisplayAlarm procedure at 5:00 p.m. on December 31, 2007:

Application.OnTime DateValue("12/31/2007 5:00 pm"), "DisplayAlarm"

This particular code line could come in handy to warn you that you need to go home and get ready for the New Year's Eve festivities.

Here's another example that uses the OnTime event. Executing the UpdateClock procedures writes the time to cell A1 and also programs another event five seconds later. This event reruns the UpdateClock procedure. The net effect is that cell A1 is updated with the current time every five seconds.

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.


NextTick As Date



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"





Cancels the OnTime event (stops the clock)

On Error Resume Next

Application.OnTime NextTick, "UpdateClock", ,




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

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

Keypress events

While you work, Excel constantly monitors what you type. Because of this, you can set up a keystroke or a key combination to execute a procedure.

Here's an example that reassigns the PgDn and PgUp keys:



Application.OnKey "{PgDn}",


Application.OnKey "{PgUp}",






On Error Resume Next

If TypeName(ActiveSheet) =

"Worksheet" _

Then ActiveCell.Offset(1,






On Error Resume Next

If TypeName(ActiveSheet) = "Worksheet" _

Then ActiveCell.Offset(-1, 0).Activate



After setting up the OnKey events by executing the Setup_OnKey procedure, pressing PgDn moves you down one row. Pressing PgUp moves you up one row.

Notice that the key codes are enclosed in braces, not parentheses. For a complete list of keyboard codes, consult the Help system. Search for OnKey.

In this example, 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 that can safely be ignored. Also, notice that the procedures check to see which type of sheet is active. The routine only does something when a worksheet is the active sheet.

By executing the following routine, you cancel the OnKey events:

Sub Cancel_OnKey()





End Sub

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. For example, the following statement tells Excel to ignore Alt+F4. The percent sign represents the Alt key:

If you close the workbook with the code and leave Excel open, the OnKey method will not be reset. As a consequence, pressing the shortcut key will cause Excel to automatically open the file with the macro. To prevent this from happening, you should include code in your Workbook_BeforeClose event code (I showed you that event earlier on in this chapter) to reset the Onkey event.

Was this article helpful?

0 0

Post a comment