Excel VBA Assign hotkey to Macro using OnKey Method

What is Macro?

Macro is a set of actions that are intended to be repeating all over again. Macro is not a word just for Excel, you can find many other software help users record a set of actions for Windows or even for specific games. The first Macro I used was EZ Macro about 15 years ago, which helped me repeat a set of actions in an online game endlessly in order to get my skill points up. Today I still use keyboard and mouse that can record Macro.

Excel is very kind to have a built-in Record Macro function, which allows you to record actions and turn them into Excel VBA code. You can quickly learn VBA by studying how the code is written for each action, but not everything can be recorded. For example, if you want to type 1 in A1, 2 in A2, 3 in A3, etc until A1000, you still need the programming knowledge to do the loop automatically, instead of recording the action manually.

Assign hotkey to Macro in workbook

If you create a Macro from “Record a Macro“, you can assign a hot key to a Macro, but the combination must include CTRL.

assignMacro1

You can ignore the restriction of CTRL combination in VBA using OnKey Method, and you can add more than 2 keys as hotkey.

Assign hotkey to Macro using OnKey Method

Syntax of OnKey Method

Application.OnKey(Key, Procedure)

Key – A string indicating the key to be pressed

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}

The above table and the below table are used to make a combination of “key” parameter. Hotkey must not be empty in order for the hotkey to work.

To combine keys with Precede the key code by
SHIFT + (plus sign)
CTRL ^ (caret)
ALT % (percent sign)

Procedure – Name of the Macro (Sub Procedure)

Example 1  – Excel VBA Assign hotkey to Macro

Assume that you have already written or recorded a Macro called Macro1. To assign hotkey Crtl+Shift+k to Macro1, we find the the code that represents “Ctrl” and “Shift” from the above tables, and below is the code we will use.

Application.Onkey "^+k", "Macro1"

To apply this code work in a worksheet, ALT+F11 > Double click on ThisWorkbook > select Workbook > Select Open

(To know more about Workbook_Open Event, click here)

hotkey_03

Insert the below code

Private Sub Workbook_Open()
    Application.OnKey "^+k", "Macro1"
End Sub

Because this event is triggered when workbook is open, you must close the workbook and then reopen it for the hotkey to work. Although this code is executed once at the moment workbook is opened, the short key works during workbook is open.

Example 2 – Override Ctr+V to Paste as Unicode

This thread was originally posted in Microsoft Community and as answered by me.

Question

I copy/paste information from web pages to Excel on a regular basis.  Currently, I am forced to copy text from web page, right click the cell in Excel, select “Paste Special…” from the context menu, the select “Unicode text” from the listed options.  Very cumbersome, and it slows me down.  Likewise the option of using Ctrl-V, then hitting the icon to bring up the Paste Options, and clicking on the icon representing “Match Destination Formating”.

To put this another way… I almost NEVER paste formatting from the web to an Excel spreadsheet… it happens so rarely, I’d be willing to go through the extra steps in that rare circumstance.

How to set this so I can Ctrl-C selected text from a web page, and Ctrl-V into a cell in Excel, to DEFAULT to Unicode text without source formatting?

I’ve tried following the directions for various earlier versions of Excel, but it’s still cumbersome.  I’m looking for ease-of-use, straight-forward functionality for my purposes, not fingers-tied-in-knots.

Answer

Actually you can override Ctrl+V behavior in VBA

Alt+F11 > Insert Module > Copy and paste the below code

Sub pasteVal()
 ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
         DisplayAsIcon:=False  
 End Sub

This time copy the below code in “ThisWorkBook” instead of new Module

Private Sub Workbook_Open()
     Application.OnKey "^v", "pasteVal"
 End Sub

Now you can use Ctrl+V to paste as Unicode

 

Outbound References

https://msdn.microsoft.com/en-us/library/office/ff197461%28v=office.15%29.aspx

 

Leave a Reply

Your email address will not be published.