How to Use Non-object Events

This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide and Learn Excel VBA Events Completely with 5 Tutorials.

• In article Workbook-level Events and Their Uses, I have discussed workbook related some important events.
• And in article Worksheet-related Events and Their Uses, I have demonstrated how to use some important worksheet events.

They all are object-related events. We call them object-related events as they work with objects like workbook and worksheet.

In this article, I am going to discuss about two events: OnTime and OnKey. These two are not object related events. Instead, we access them as the methods of the Application object.

Note: We used relevant VBA code module to write our previous workbook and worksheet related event-handler procedures. We shall use general VBA module to write OnTime and OnKey events.

Using Non-object Event: OnTime

The OnTime event executes at a specified time. In the following example, I have programmed Excel to beep and then display a message at 3 p.m.:

Sub SetAlarm()
    Application.OnTime 0.625, "DisplayAlarm"
End Sub

Sub DisplayAlarm()
    Beep
    MsgBox "Wake up. You are late buddy!"
End Sub

In the above example, we have two procedures: SetAlarm() and DisplayAlarm. SetAlarm() procedure has one statement: Application.OnTime 0.625, “DisplayAlarm”. With this statement Application object’s OnTime method is used to set up the OnTime event.

OnTime method has two arguments:

• Time: In this example time is 0.625. It is actually 3 P.M. 3 P. M. in 24-hour system is 15:00. 15/24=0.625.
• Procedure Name: This procedure will execute when the time occurs. In our example the procedure is: DisplayAlarm. When time is 3 p.m., DisplayAlarm procedure will execute.

Sometimes it is difficult to understand Excel’s time numbering system, so you can use VBA’s TimeValue function. The following statement can be the alternative of this statement: Application.OnTime 0.625, “DisplayAlarm”

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

You may also schedule an event that’s relative to current time. For example, 30 minutes from now. To do this you have to write the instruction in the following way:

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

It is possible to use the OnTime method to execute the OnTime event on particular day. Of course, you have to keep your computer turned on and your Excel running.

Read More: Two types of VBA Macros: VBA Sub procedures & VBA functions

Using Non-object Event: OnKey

While we work in Excel, Excel constantly monitors what we do. Sometimes you may want to perform something when you press a particular keystroke or a combination of keystrokes.

In my following example, I have setup “pg dn” or “pg up” keystrokes to do something when someone presses them.

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

Note: The key codes are enclosed in brackets, not in parentheses. VBA Help provides the complete list of keyword codes.

Tip: I have used On Error Resume Next to ignore any errors generated in the procedure. For example, if the active cell is in the first row, and you try to move up by one row will cause an error. If the sheet is a chart sheet, an error occurs because there does not exist any active cell in a chart sheet.

You can cancel the effects of OnKey events. Use the following procedure to do so:

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

Caution: Observe this statement: Application.OnKey “{F4}”, “”. What do you think this statement will do? You may think that empty second argument will cancel the OnKey effect of F4. This is not correct. This statement will just do nothing when someone presses F4 key.

For example, the following instruction tells Excel to ignore Alt+F4. Application.OnKey “%{F4}”, “”. (the percent sign represents the Alt key).

So, this was all from me on this topic. Let me know in case you face any issues while using this article.

Happy Excelling ☕

Related Articles

• What are Excel Events and Their Types
• Introduction to VBA Macros

Kawser

Hello!
Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!