It is always nice to have keyboard shortcuts for executing functions/subroutines rather than having to click the menu and its command
One way will to link the macro to a command button and assign the shortcut for the button. The other one is to assign the shortcut to the function using Application.OnKey
OnKey method executes a specified procedure when a particular key or key combination is pressed
Application.OnKey "%b", "ToWord"
is used to trigger the "ToWord" subroutine whenever Alt+b is pressed . Percentage symbol is used to substitute Alt key
Use the Caret (^) to symbol for Ctrl key and plus (+) for Shiftkey
Application.OnKey "^b", "ToWord"
Is for Ctrl + b
Other keys are :
| 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} |
To deassign/release the shortcut leave the Procedure empty
Application.OnKey "%b", ""
Disable Save (Ctrl S)
Application.OnKey "^s", ""
Excel handles shortcut keys very bad. It does much better job with toolbar button. For example, if a user clicks "^c" for a workbook, there is no way for another Excel workbook to know that unless it implements:
ReplyDeleteApplication.OnKey "^c", "MyProcedure"
This prevents other workbooks from listening.
However using Copy menu item or copy toolbar button fires the click event which can be received by all workbooks.
So, I suggest that whenever some one needs to handle a shortcut, MyProcedure should be like that
Public sub MyProcedure()
refCopyButton.Excecute
end sub
Then Handle the click event as you desire:
private sub refCopyButton_click()
' do what you want
end sub
I have been trying to figure out how to reassign macros to symbols (e.g. format to #.x by pressing control + shift + 8) for a long time. Thank you so much for this posting.
ReplyDeleteCan someone please explain to me where I am suppose to put the application.onkey line of code?
ReplyDeleteYou can use this in the Workbooks_Open event of Addin_Install event and then reset the ye in Close or Uninstall events respectively
ReplyDeleteIs it possible to reset shortcuts to user defined shortcuts rather than excel default shortcuts otherwise i can see this causing problems where workbooks are shared and the recipient has defined their own shortcut keys.
ReplyDeleteWorks Great Thanks!
ReplyDeletePrivate Sub Workbook_AddinInstall()
Application.OnKey "^t", "MISC.OutlineLines"
End Sub
Where in the module is thus call intended to reside?
ReplyDeleteIs it supposed to be part of the Sub or does it require it's own sub?
wen i assigned CTRL+1 as a shortcut for a macro it works fine on the normal "1" key but it doesn work on the "1" key in the numpad, how do i assign it for that??
ReplyDelete