Disable key or key combination or run a macro if you use it

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

How do I use Application.Onkey

With Application.Onkey you can disable a particular key or key combination or run a macro when you use a particular key or key combination. Below you can read information from Excel's VBA help about Onkey.

The Key argument can specify any single key combined with ALT, CTRL, or SHIFT, or any combination of these keys. Each key is represented by one or more characters, such as "a" for the character a, or "{ENTER}" for the ENTER key.

Shift key = "+" (plus sign)
Ctrl key = "^" (caret)
Alt key = "%" (percent sign)

This example assigns "YourMacroName" to the key sequence SHIFT+CTRL+RIGHT ARROW
Application.OnKey "+^{RIGHT}", "YourMacroName"

This example returns SHIFT+CTRL+RIGHT ARROW to its normal meaning.
Application.OnKey "+^{RIGHT}"

This example disables the SHIFT+CTRL+RIGHT ARROW key sequence.
Application.OnKey "+^{RIGHT}", ""

You can also use it to disable a built-in shortcut like Ctrl p that you can use to Print.
Application.OnKey "^p", ""

Note: For some reason you can't disable every key combination with Onkey. I am not able to disable Ctrl- and Ctrl+ for example but you can protect the worksheet to disable these two shortcuts that popup the Insert and Delete dialog.

Tip: If you want to use Onkey only for one workbook you can place the code in the Activate and Deactivate event in the ThisWorkbook module of that file.

If you have no idea where to paste the code then check out this page.
Where do I paste the code that I find on the internet

Private Sub Workbook_Activate()
    Application.OnKey "+^{RIGHT}", "YourMacroName"
End Sub

Private Sub Workbook_Deactivate()
    Application.OnKey "+^{RIGHT}"
End Sub

Disable almost every key or key combination

If you want to disable every key or key combination you can start with the code below that was posted a long time ago in the Excel newsgroup. If you have suggestions to make the code better let me know.

Sub Disable_Keys()
    Dim StartKeyCombination As Variant
    Dim KeysArray As Variant
    Dim Key As Variant
    Dim I As Long

    On Error Resume Next

    'Shift key = "+"  (plus sign)
    'Ctrl key = "^"   (caret)
    'Alt key = "%"    (percent sign
    'We fill the array with this keys and the key combinations
    'Shift-Ctrl, Shift- Alt, Ctrl-Alt, Shift-Ctrl-Alt

    For Each StartKeyCombination In Array("+", "^", "%", "+^", "+%", "^%", "+^%")

        KeysArray = Array("{BS}", "{BREAK}", "{CAPSLOCK}", "{CLEAR}", "{DEL}", _
                    "{DOWN}", "{END}", "{ENTER}", "~", "{ESC}", "{HELP}", "{HOME}", _
                    "{INSERT}", "{LEFT}", "{NUMLOCK}", "{PGDN}", "{PGUP}", _
                    "{RETURN}", "{RIGHT}", "{SCROLLLOCK}", "{TAB}", "{UP}")

        'Disable the StartKeyCombination key(s) with every key in the KeysArray
        For Each Key In KeysArray
            Application.OnKey StartKeyCombination & Key, ""
        Next Key

        'Disable the StartKeyCombination key(s) with every other key
        For I = 0 To 255
            Application.OnKey StartKeyCombination & Chr$(I), ""
        Next I

        'Disable the F1 - F15 keys in combination with the Shift, Ctrl or Alt key
        For I = 1 To 15
            Application.OnKey StartKeyCombination & "{F" & I & "}", ""
        Next I

    Next StartKeyCombination


    'Disable the F1 - F15 keys
    For I = 1 To 15
        Application.OnKey "{F" & I & "}", ""
    Next I

    'Disable the PGDN and PGUP keys
    Application.OnKey "{PGDN}", ""
    Application.OnKey "{PGUP}", ""
End Sub



Sub Enable_Keys()
    Dim StartKeyCombination As Variant
    Dim KeysArray As Variant
    Dim Key As Variant
    Dim I As Long

    On Error Resume Next

    'Shift key = "+"  (plus sign)
    'Ctrl key = "^"   (caret)
    'Alt key = "%"    (percent sign
    'We fill the array with this keys and the key combinations
    'Shift-Ctrl, Shift- Alt, Ctrl-Alt, Shift-Ctrl-Alt

    For Each StartKeyCombination In Array("+", "^", "%", "+^", "+%", "^%", "+^%")

        KeysArray = Array("{BS}", "{BREAK}", "{CAPSLOCK}", "{CLEAR}", "{DEL}", _
                    "{DOWN}", "{END}", "{ENTER}", "~", "{ESC}", "{HELP}", "{HOME}", _
                    "{INSERT}", "{LEFT}", "{NUMLOCK}", "{PGDN}", "{PGUP}", _
                    "{RETURN}", "{RIGHT}", "{SCROLLLOCK}", "{TAB}", "{UP}")

        'Enable the StartKeyCombination key(s) with every key in the KeysArray
        For Each Key In KeysArray
            Application.OnKey StartKeyCombination & Key
        Next Key

        'Enable the StartKeyCombination key(s) with every other key
        For I = 0 To 255
            Application.OnKey StartKeyCombination & Chr$(I)
        Next I

        'Enable the F1 - F15 keys in combination with the Shift, Ctrl or Alt key
        For I = 1 To 15
            Application.OnKey StartKeyCombination & "{F" & I & "}"
        Next I

    Next StartKeyCombination


    'Enable the F1 - F15 keys
    For I = 1 To 15
        Application.OnKey "{F" & I & "}"
    Next I

    'Enable the PGDN and PGUP keys
    Application.OnKey "{PGDN}"
    Application.OnKey "{PGUP}"
End Sub

More Information

Check out also this page :

Disable Excel 2003 Menu Accelerators keys in Excel 2007-2013

Ron de BruinExcel Automation