Introduction to the VBA OnKey Event
The OnKey event is mainly executed by the Application.OnKey method.
Arguments:
♣ key argument – String-type data that typically takes the key or combination of keys pressed for a certain event.
♣ The procedure argument – Variant type data that contains the event that you are going to perform for certain keys.
How to Use OnKey Non-Object Event in Excel VBA
We will make the sheet scroll up for the Pg Up keystroke and scroll down for the Pg Dn keystroke.
Steps:
- Go to the Developer tab and open Visual Basic.
- The Microsoft Visual Basic for Applications window will appear.
- Go to the Insert tab and choose the Module option.
- A new module named Module1 will be created.
- Double-click on Module1 and insert the following code in the code window.
Option Explicit
Sub VBAOnKey()
Application.OnKey Key:="{PGDN}", Procedure:="pageDown"
Application.OnKey Key:="{PGUP}", Procedure:="pageUp"
End Sub
Private Sub pageDown()
ActiveWindow.SmallScroll down:=1
End Sub
Private Sub pageUp()
ActiveWindow.SmallScroll up:=1
End Sub- Press Ctrl + S.
- The Microsoft Excel dialogue box will appear. Click on No.
- The Save As dialogue box will appear.
- Choose the Save as type: option as .xlsm format and click on the Save button.
- Close the code window, go to the Developer tab, and select Macros.
- The Macro window will appear.
- Choose the macro VBAOnKey and click on the Run button.
- You have enabled your VBA OnKey event.
- If you press the Page Down key, Excel scrolls down. If you press the Page Up key, you’ll scroll up.
The key codes are enclosed in brackets, not in parentheses. VBA Help provides the complete list of keyword codes.
Tip:
We 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 the OnKey events. Use the following procedure to do so:
Sub Cancel_VBAOnKey()
Application.OnKey Key:="{PGDN}"
Application.OnKey Key:="{PGUP}"
End SubRead More: Excel VBA Events
VBA OnKey Event: 2 More Examples
Example 1 – Disable Arrow Keys
- Use the following code:
Sub DisableArrowKeys()
Application.OnKey "{UP}", ""
Application.OnKey "{DOWN}", ""
Application.OnKey "{LEFT}", ""
Application.OnKey "{RIGHT}", ""
End SubRead More: Excel VBA: Workbook Level Events and Their Uses
Example 2 – Disable SHIFT + CTRL + Right Arrow Keys Functionality
Generally, if you press SHIFT + CTRL + Right Arrow, Excel selects all cells at the right of the active cell. You can disable this by using the following code:
Sub Disable_Shift_Ctrl_RightArrow()
Application.OnKey "+^{RIGHT}", ""
End SubList of Key Codes for OnKey Event
| Key | Code |
|---|---|
| BACKSPACE | {BACKSPACE} or {BS} |
| BREAK | {BREAK} |
| CAPS LOCK | {CAPSLOCK} |
| CLEAR | {CLEAR} |
| DELETE or DEL | {DELETE} or {DEL} |
| DOWN ARROW | {DOWN} |
| END | {END} |
| ENTER (numeric keypad) | {ENTER} |
| ENTER | ~ (tilde) |
| ESC | { ESCAPE} or {ESC} |
| HELP | {HELP} |
| HOME | {HOME} |
| INS | {INSERT} |
| LEFT ARROW | {LEFT} |
| NUM LOCK | {NUMLOCK} |
| PAGE DOWN | {PGDN} |
| PAGE UP | {PGUP} |
| RETURN | {RETURN} |
| RIGHT ARROW | {RIGHT} |
| SCROLL LOCK | {SCROLLLOCK} |
| TAB | {TAB} |
| UP ARROW | {UP} |
| F1 through F15 | {F1} through {F15} |
You can also apply key combinations to initiate a keypress event. To combine Shift / Ctrl / Alt keys, precede the keys with the codes given below.
| To combine keys with | Precede the key code by |
|---|---|
| Shift | + (plus sign) |
| Ctrl | ^ (caret) |
| Alt | % (percent sign) |
Download the Practice Workbook
Related Articles
- Excel VBA Open Workbook Event
- How to Create Calendar Using VBA in Excel
- How to Use VBA Event Handler
I store these Non-Object events in Personal file Module, but nothing gets done.
Any idea why…?
Hello Tomas,
You can store these non-object events in a personal file module. The solution is applicable to any module. First, you need to define the time value perfectly. To do that, you may use the following code.
Application.OnTime TimeValue(“11:48:00 am”), “DisplayAlarm”
If you set the time value properly, then there will be no problem in any module. Hope you get your required solution.