You can use the OnKey method to assign a macro procedure to a single keystroke or any combination of Ctrl, Shift, and Alt with another key. You can also use the method to disable key combinations.

The following example shows how to assign the DownTen macro to the down arrow key. Once AssignDown has been run, the down arrow key will run the DownTen macro and move the cell pointer down ten rows instead of one:

Sub AssignDown()

Application.OnKey "{Down}", "DownTen" End Sub

Sub DownTen()

ActiveCell.Offset(1Q, 0).Select

End Sub

Sub ClearDown()

Application.OnKey "{Down}" End Sub

ClearDown returns the down arrow key to its normal function.

OnKey can be used to disable existing keyboard shortcuts. You can disable the Ctrl+c shortcut, normally used to copy, with the following code that assigns a null procedure to the key combination:

Sub StopCopyShortCut()

Application.OnKey "Ac", "" End Sub

Note that a lowercase c is used. If you used an uppercase C, it would apply to Ctrl+Shift+c. Once again, you can restore the normal operation of Ctrl+c with the following code:

Sub ClearCopyShortCut()

Application.OnKey "Ac" End Sub

The key assignments made with the OnKey method apply to all open workbooks and only persist during the current Excel session.

0 0

Post a comment