EXCELY


Free Excel Templates and Articles
  • Excel VBA
  • Assign Hotkey to SmartDel Macro using Excel VBA

Assign Hotkey to SmartDel Macro using Excel VBA



Easy way

  • Open the Tools menu, choose Macro then Macros
  • Select the macro SmartDel
  • Click on the button [Options]
  • Set the Shortcut key <CTRL> + <Symbol>
  • Done!

But this method doesn't allow set hotkey like <CTRL> + <Del> or <ALT> + <Del>.
Advanced way
You can use Application.OnKey Method. This method runs a procedure when a particular key combination.

Application.OnKey(Key, Procedure)
Key - A string indicating the key to be pressed
Procedure - A string indicating the name of the procedure to be run

To specify characters that aren't displayed when you press the corresponding key (BACKSPACE, for example), use the codes listed in the following table

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                  ~ (tilde)
ENTER (numeric keypad) {ENTER}
ESC                    {ESCAPE} or {ESC}
F1 through F15         {F1} through {F15}
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}

You can also specify keys combined with SHIFT, CTRL and ALT:

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

So string “%{DELETE}” is mean hotkey <ALT> + <Del>.
The code in the sub Workbook_Open will be executed whenever this worksheet is loaded:

Private Sub Workbook_Open()
    Application.OnKey "%{DELETE}", "SmartDel"
End Sub

Done! Now we can select any range and press <ALT> + <Del> to run SmartDel macro.