VBA实用小程序：禁用/启用所有Excel快捷键

如果你不想使用Excel内置的快捷键，或者不希望你开发的界面因为快捷键而被破坏，你可能会想禁用所有的Excel快捷键。

下面的程序禁用所有的Excel快捷键：

代码语言：javascript复制

Sub Disable_Keys()
    Dim StartKeyCombination As Variant
    Dim KeysArray As Variant
    Dim Key As Variant
    Dim I As Long
    On Error Resume Next
   'Shift键 = " "  (加号)
   'Ctrl键 = "^"   (脱字号)
   'Alt键 = "%"    (百分号)
   '使用这些键及其组合填充数组
   '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}")
        '禁用StartKeyCombination中每个键与KeysArray中的组合键
        For Each Key In KeysArray
           Application.OnKey StartKeyCombination & Key, ""
        Next Key
        '禁用StartKeyCombination中每个键与其它键的组合键
        For I= 0 To 255
           Application.OnKey StartKeyCombination & Chr$(I), ""
        Next I
        '禁用F1 -F15键与Shift, Ctrl 或 Alt 键的组合键
        For I= 1 To 15
           Application.OnKey StartKeyCombination & "{F" & I &"}", ""
        Next I
    Next StartKeyCombination
   '禁用F1 - F15 键
    For I = 1 To 15
       Application.OnKey "{F" & I & "}",""
    Next I
   '禁用PGDN和PGUP键
   Application.OnKey "{PGDN}", ""
   Application.OnKey "{PGUP}", ""
End Sub

下面的程序启用所有的Excel快捷键：

代码语言：javascript复制

Sub Enable_Keys()
    Dim StartKeyCombination As Variant
    Dim KeysArray As Variant
    Dim Key As Variant
    Dim I As Long
    On Error Resume Next
   'Shift键 = " "  (加号)
   'Ctrl键 = "^"   (脱字号)
   'Alt键 = "%"    (百分号)
   '使用这些键及其组合填充数组
   '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}")
        '启用StartKeyCombination中每个键与KeysArray中的组合键
        For Each Key In KeysArray
           Application.OnKey StartKeyCombination & Key
        Next Key
        '启用StartKeyCombination中每个键与其它键的组合键
        For I= 0 To 255
           Application.OnKey StartKeyCombination & Chr$(I)
        Next I
        '启用F1 -F15键与Shift, Ctrl 或 Alt 键的组合键
        For I= 1 To 15
           Application.OnKey StartKeyCombination & "{F" & I &"}"
        Next I
    Next StartKeyCombination
   '启用 F1 - F15 键
    For I = 1 To 15
       Application.OnKey "{F" & I & "}"
    Next I
   '启用PGDN和PGUP键
   Application.OnKey "{PGDN}"
   Application.OnKey "{PGUP}"
End Sub

你可以在工作簿打开时禁用所有快捷键，在关闭工作簿时恢复所有快捷键，这需要你在ThisWorkbook模块中放置下面的代码：

代码语言：javascript复制

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Enable_Keys
End Sub
Private Sub Workbook_Open()
    Disable_Keys
End Sub

建议在这个工作簿开发时，在功能区或者工作表界面放置一个恢复所有快捷键的按钮，单击该按钮可以恢复所有快捷功能，以避免在需要时不方便编辑代码的情形。

excel 程序 工作 快捷键

0 人点赞