Excel VBA Worksheet事件问题：InputBox失效及保护工作表报错

问题根源 #

原代码存在几个核心问题：

• 修改单元格属性时会重复触发Worksheet_Change事件，导致InputBox异常
• 工作表保护时未开放格式修改权限，指定区域外单元格无法修改颜色
• 空值场景下没有正确处理单元格锁定状态和工作表保护逻辑
• 缺少错误捕获，一旦出错会导致事件触发失效

修复后的代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim X As Range
    Dim userPass As String
    Dim originalPass As String
    originalPass = "Kals"
    
    ' 禁用事件，防止修改单元格时反复触发Change事件
    Application.EnableEvents = False
    
    Set X = Union(Me.Range("I3:I9"), Me.Range("K3:K9"))
    
    On Error GoTo ErrorHandler ' 出错时确保能恢复事件和保护状态
    
    ' 处理指定区域内的单元格变更
    If Not Application.Intersect(Target, X) Is Nothing Then
        Me.Unprotect Password:=LCase(originalPass)
        
        If Target.Value = "" Then
            ' 空值时解锁单元格，方便后续编辑
            Target.Locked = False
        Else
            userPass = InputBox("请输入密码", "密码验证")
            Target.Locked = True
            ' 用用户输入的密码保护工作表，同时允许修改单元格格式
            Me.Protect Password:=LCase(userPass), AllowFormattingCells:=True
        End If
    Else
        ' 处理指定区域外的单元格，允许修改颜色等格式
        Me.Unprotect Password:=LCase(originalPass)
        ' 这里可添加自定义颜色修改逻辑，例：Target.Interior.Color = RGB(255,255,0)
        Me.Protect Password:=LCase(originalPass), AllowFormattingCells:=True
    End If

ErrorHandler:
    ' 恢复事件触发，避免后续Change事件失效
    Application.EnableEvents = True
    ' 确保工作表最终处于保护状态
    If Not Me.ProtectContents Then
        Me.Protect Password:=LCase(originalPass), AllowFormattingCells:=True
    End If
End Sub

核心修复点说明 #

• 禁用事件触发：添加Application.EnableEvents = False，彻底解决InputBox反复弹出或无响应的问题
• 开放格式权限：保护工作表时加上AllowFormattingCells:=True，允许修改单元格颜色等格式
• 完善空值处理：空值时解锁单元格，避免后续无法编辑该单元格
• 错误兜底：错误捕获分支确保即使代码出错，事件也能恢复，工作表不会一直处于未保护状态
• 指定区域外逻辑：单独处理区域外单元格的格式修改需求，解锁后修改再恢复保护

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