While we work in Excel, Excel constantly monitors what we do. Sometimes you may want to perform something when you press a particular keystroke for easier and quicker use. You can achieve this using the OnKey event. In this article, I will show you a practical example of the VBA OnKey event with step-by-step guidelines.
Introduction to VBA OnKey Event
VBA OnKey event is mainly executed by the Application.OnKey method.
Arguments:
It mainly contains two arguments in the syntax.
- One is the key.
- And another is the procedure.
♣ key argument- It is mainly string-type data and it mainly takes the key or combination of keys that you are going to press for a certain event.
♣ The procedure argument- It is mainly variant type data and it mainly contains the event that you are going to perform for certain keys.
How to Use OnKey Non-Object Event in Excel VBA
In this example, we would use the OnKey event to set up “Pg Dn” or “Pg Up” keystrokes to do something when someone presses them. We will set this as the sheet will scroll up for the Pg Up keystroke and will scroll down for the Pg Dn keystroke. Now, to accomplish this, go through the steps below:
📌 Steps:
- First and foremost, go to the Developer tab >> Visual Basic tool.
- As a result, the Microsoft Visual Basic for Applications window will appear.
- Following, go to the Insert tab here >> choose the Module option.
- As a result, a new module named Module1 will be created.
- Subsequently, double-click on Module1 and write 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- Afterward, press Ctrl + S.
- As a result, the Microsoft Excel dialogue box will appear. Click on the No button.
- At this time, the Save As dialogue box will appear.
- Following, choose the Save as type: option as .xlsm format and click on the Save button.
- Afterward, close the code window and go to the Developer tab >> Macros tool.
- As a result, the Macro window will appear.
- Subsequently, choose the macro VBAOnKey and click on the Run button.
Consequently, you have enabled your VBA OnKey event. And, if you press the Page Down key, you will see you are scrolling downward and if you press the Page Up key, you will see you are scrolling upward.
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. Besides, if the sheet is a chart sheet, an error occurs because there does not exist any active cell in a chart sheet.
Moreover, 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
1. Disable Arrow Keys
At this time, say, you want to disable your arrow keys now. You can follow the steps stated above and apply the following code subsequently in the VB Editor to accomplish this.
Sub DisableArrowKeys()
Application.OnKey "{UP}", ""
Application.OnKey "{DOWN}", ""
Application.OnKey "{LEFT}", ""
Application.OnKey "{RIGHT}", ""
End SubAfter applying this code, you will see that your arrow keys will not work anymore.
Read More: Excel VBA: Workbook Level Events and Their Uses
2. Disable SHIFT+CTRL+Right Arrow Keys Functionality
Generally, if we press SHIFT+CTRL+Right Arrow, we can select the cells at the right of the active cell. However, you can disable this by using the code in the stated above procedures.
Sub Disable_Shift_Ctrl_RightArrow()
Application.OnKey "+^{RIGHT}", ""
End SubLikewise, you can apply many VBA codes to initiate keypress events. To initiate keypress events, you must know the list of codes to express keys. Here is the list below for keys.
List 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} |
Moreover, you can also apply key combinations to initiate a keypress event. To combine Shift / Ctrl / Alt keys, precede the keys as the codes given below.
| To combine keys with | Precede the key code by |
|---|---|
| Shift | + (plus sign) |
| Ctrl | ^ (caret) |
| Alt | % (percent sign) |
Download Practice Workbook
You can download our practice workbook from the following button for free.
Conclusion
So, in this article, I have shown you a VBA OnKey event briefly. I suggest you study this article very well and practice accordingly. Besides, you can download our free practice workbook here to practice on your own. I hope you find this article helpful and informative. Please feel free to comment below if you have any queries or recommendations.
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.