如何在数据验证单元格无效时退出WorksheetChange事件

针对数据验证单元格输入无效值后点击「取消」导致WorksheetChange事件中Application.Undo报错的问题，可以通过跟踪单元格原始值的方式判断是否为数据验证恢复操作，从而跳过后续代码执行。

实现代码 #

在目标工作表的模块中添加以下代码：

' 模块级变量，用于记录选中单元格的原始值和地址
Private prevCellValue As Variant
Private prevCellAddress As String

' 记录选中单元格的初始状态
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        prevCellValue = Target.Value
        prevCellAddress = Target.Address
    Else
        prevCellValue = Empty
        prevCellAddress = ""
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 检查是否是数据验证恢复导致的Change事件
    Dim hasDataValidation As Boolean
    ' 判断单元格是否有数据验证（忽略无验证时的错误）
    On Error Resume Next
    hasDataValidation = (Target.Validation.Type <> xlValidateNone)
    On Error GoTo 0
    
    ' 单个单元格+有数据验证+值未变化（恢复到原始值）= 数据验证取消操作
    If Target.Cells.Count = 1 And hasDataValidation Then
        If Target.Address = prevCellAddress And Target.Value = prevCellValue Then
            Exit Sub ' 跳过后续自动化操作
        End If
    End If
    
    ' ----------------------
    ' 以下是你的原有自动化代码
    ' ----------------------
    
    ' 示例：你的Application.Undo操作（添加错误捕获作为双重保险）
    On Error Resume Next
    Application.Undo
    On Error GoTo 0
End Sub

逻辑说明 #

1. 记录初始状态：通过Worksheet_SelectionChange记录用户选中单个单元格时的原始值和地址，确保只跟踪用户可能编辑的单元格。
2. 判断数据验证恢复：在Worksheet_Change中，先确认目标单元格有数据验证，再对比当前值与记录的原始值——如果地址匹配且值未变化，说明是数据验证将无效输入恢复为原始值，直接退出事件。
3. 双重保险：在Application.Undo处添加错误捕获，即使判断逻辑有遗漏，也能避免报错中断程序。

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