VBA宏触发太频繁

Question

我的工作表设置了数据验证下拉列表，我希望宏仅在单元格的值从下拉列表中的另一个值更改时触发，而不是从默认的“空”值更改。

这是我正在尝试使用的:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 5 Then
        If IsEmpty(Target.Value) = True Then
            MsgBox "Test1"
        Else
            MsgBox "Test2"
        End If
    End If

exitHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

我的问题是这个“IsEmpty”命令是在选择之后而不是之前读取单元格。我希望它在选择之前而不是之后读取单元格的值。

我怎样才能做到这一点？

Answer 1

示例方法:

Const COL_CHECK As Long = 5
Private oldVal

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    Set c = Target.Cells(1) '<< in case multiple cells are changed...
    If c.Column = COL_CHECK Then
        If oldVal <> "" Then
            Debug.Print "changed from non-blank"
        Else
            Debug.Print "changed from blank"
        End If
    End If

exitHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim c As Range
    Set c = Target.Cells(1)
    oldVal = IIf(c.Column = COL_CHECK, c.Value, "")
    Debug.Print "oldVal=" & oldVal
End Sub