Application.OnKey

It is possible to run your macros when a particular key or key combination is pressed.
This method can only be used to assign macros to shortcut keys.
This can be done by invoking the OnKey method where 'key' is the key combination and 'procedure' is the name of the macro.
Any key assignments made are applied to all open workbooks and only persist in the current Excel session.


Excel

The 'key' can specify any single key or any key combined with one or more of the Alt, Ctrl or Shift keys.

Application.OnKey(key, procedure) 


Application.OnKey(key) - restore hotkey assignment to normal
Application.OnKey(key, "") - disables hotkey


Application.OnKey("^c", "myMacro") 
Application.OnKey("{Up}", "CustomMoveUpMacro")


Normal alphanumeric keys are denoted by themselves (e.g. "a", "A", "1").
It is possible to use special keys as well although these have special codes that must be used.
For a full list of all the special keys that can be used, please refer to the VBA > Macros > Sendkeys page.
In order to use one of the following characters + ^ % { ) ~ without having it interpreted as a special key just enclose it in curley braces.

Application.OnKey "{%}" 


Word

This method does not exist in Word at the moment.


PowerPoint

This method does not exist in PowerPoint at the moment.


Changing Built-in Behaviour

It is also possible to use this method to change the normal behaviour of Excel by assigning a key combination that Excel uses already by default, such as (Ctrl + S) for save.
We can disable a built-in shortcut key by assigning it to an empty string. The following line of code disables the (Ctrl + S) shortcut key.

Application.OnKey "^o", "" 

To enable the shortcut key back to its original functionality you can use the following line of code.

Application.OnKey "^o" 

© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

Top

PrevNext