Saturday, June 02, 2007

Assigning Shortcut Keys - Excel Macros

Shortcut Key Assignment for Subroutines


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", ""



8 comments:

  1. Anonymous12:40 PM

    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:

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

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

    ReplyDelete
  3. Anonymous7:53 PM

    Can someone please explain to me where I am suppose to put the application.onkey line of code?

    ReplyDelete
  4. You can use this in the Workbooks_Open event of Addin_Install event and then reset the ye in Close or Uninstall events respectively

    ReplyDelete
  5. ShaneK1:11 AM

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

    ReplyDelete
  6. Anonymous12:28 PM

    Works Great Thanks!

    Private Sub Workbook_AddinInstall()
    Application.OnKey "^t", "MISC.OutlineLines"
    End Sub

    ReplyDelete
  7. Anonymous4:32 AM

    Where in the module is thus call intended to reside?
    Is it supposed to be part of the Sub or does it require it's own sub?

    ReplyDelete
  8. 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

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.