Excel VBA禁用快捷键代码为何偶尔随机阻止数据录入？

问题描述 #

我编写了一段Excel VBA代码用于禁用几乎所有快捷键，但它偶尔会随机阻止数据录入——选中单元格或双击进入编辑模式后，无论输入大小写字符都无响应。即使隔离该代码、移除其他宏，问题仍会偶尔出现，关闭并重新打开文件可暂时解决，但之后仍会复发。使用版本为Excel 2021 Pro Plus LTSC。

原代码如下：

Option Explicit
Rem mod__ShortCutKeys

' Ctrl = "^"
' Alt = "%"
' Shift = "+"
' Combinations ("^", "%", "+", "^%", "^+", "%+", "^%+")

Rem Declare Variables
Dim Ctrl As String
Dim Alt As String
Dim Shift As String
Dim CtrlAlt As String
Dim CtrlShift As String
Dim AltShift As String
Dim CtrlAltShift As String

Dim KeyArray As Variant
Dim Key As Variant
Dim i As Long

Rem Public Sub ShortCutKeysInitializeVariables
Public Sub ShortCutKeysInitializeVariables()

    Ctrl = "^"
    Alt = "%"
    Shift = "+"
    CtrlAlt = "^%"
    CtrlShift = "^+"
    AltShift = "%+"
    CtrlAltShift = "^%+"

    KeyArray = Array(Ctrl, Alt, Shift, CtrlAlt, CtrlShift, AltShift, CtrlAltShift)

End Sub

Rem Public Sub ShortCutKeysDisable
Sub ShortCutKeysDisable()

    Rem Calls
    Call ShortCutKeysInitializeVariables

    ' Combinations
    For Each Key In KeyArray
        For i = 1 To 255
            On Error Resume Next
            Application.OnKey Key & Chr(i), ""
        Next i
    Next Key

    ' Functions keys
    For i = 1 To 12
        On Error Resume Next
        Application.OnKey "{F" & i & "}", ""
    Next i

    ' Combinations & Functions keys
    For Each Key In KeyArray
        For i = 1 To 12
            On Error Resume Next
            Application.OnKey Key & "{F" & i & "}", ""
        Next i
    Next Key

End Sub

问题原因 #

原代码通过遍历所有ASCII字符与修饰键的组合，用Application.OnKey绑定空操作来禁用快捷键，但这种盲目遍历会意外覆盖Excel编辑模式下的基础输入逻辑。比如退格、回车这类控制字符，甚至普通字母的修饰键组合被禁用后，会导致编辑模式下输入无响应。加上On Error Resume Next隐藏了错误，无法及时发现关键按键被错误修改的问题。

修复方案 #

1. 精准禁用快捷键，避免盲目遍历 #

只针对需要禁用的特定快捷键（比如复制、粘贴、保存等）进行设置，不要覆盖所有字符组合：

Option Explicit
Rem mod__ShortCutKeys

' 自定义需要禁用的快捷键列表，按需添加/删除
Dim DisabledKeys As Variant

Public Sub ShortCutKeysInitializeVariables()
    DisabledKeys = Array( _
        "^c", "^v", "^x", "^z", "^y", _  ' 复制、粘贴、剪切、撤销、重做
        "%{F10}", "^s", "^o", "^n", _    ' 菜单栏、保存、打开、新建
        "{F1}", "{F5}", "{F12}" _        ' 帮助、刷新、另存为
    )
End Sub

Sub ShortCutKeysDisable()
    Dim key As Variant
    Call ShortCutKeysInitializeVariables
    
    ' 逐个禁用指定快捷键
    For Each key In DisabledKeys
        On Error Resume Next
        Application.OnKey key, ""
        On Error GoTo 0  ' 恢复错误捕获，避免隐藏其他问题
    Next key
End Sub

2. 加入工作簿事件，避免影响全局 #

添加工作簿激活/失活事件，仅在当前工作簿打开时禁用快捷键，切换到其他文件时恢复默认：

' 放在ThisWorkbook模块中
Private Sub Workbook_Activate()
    Call ShortCutKeysDisable
End Sub

Private Sub Workbook_Deactivate()
    ' 恢复所有快捷键的默认行为
    Application.OnKey ""
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' 关闭文件前恢复默认快捷键
    Application.OnKey ""
End Sub

3. 关键说明 #

• 按需修改DisabledKeys数组，只保留需要禁用的快捷键，不要包含普通输入相关的按键
• 移除原代码中遍历所有ASCII字符的逻辑，彻底避免破坏编辑模式的输入绑定
• 加入错误捕获的重置（On Error GoTo 0），方便排查后续可能出现的问题

内容的提问来源于stack exchange，提问作者Jawad