How to Use VBA OnKey Event in Excel (with Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Access the 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.

Insert a Module to Initiate a VBA OnKey Event

  • 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.

Write Code to Apply VBA OnKey Event

  • As a result, the Microsoft Excel dialogue box will appear. Click on the No button.

Microsoft Excel Dialogue Box

  • 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.

Save the Excel File as .xlsm Format

  • Afterward, close the code window and go to the Developer tab >> Macros tool.

Access the Macros Tool

  • As a result, the Macro window will appear.
  • Subsequently, choose the macro VBAOnKey and click on the Run button.

Run VBA OnKey Macro to Apply VBA OnKey Event

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.

Application of VBA OnKey Event

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. 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 Sub

Read 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 Sub

After 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 Sub

Likewise, 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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

2 Comments
  1. I store these Non-Object events in Personal file Module, but nothing gets done.
    Any idea why…?

  2. 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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo