我目前正在使用VBA检查某一列中的单元格何时发生更改,因此我可以调用其他宏对它们进行排序。这非常有效,只是每当我插入新行时它也会触发。因此,我使用IsEmpty添加了一个检查,以查看有问题的单元格是否为空。但我显然做错了,因为每当我插入一行时,我的宏仍然会被调用。我做错了什么?
触发单元格更改的VBA:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("A:A")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
If Not IsEmpty(KeyCells) Then
Call SortByDate
End If
End If
End Sub
您可以通过检查收到更改的单元格数量来过滤行插入。在插入行的情况下,这大于或等于工作表的columns.count
。如果您正在更改该工作表上的任何内容,请在开始更改任何内容之前使用application.enableevents = false
,在离开子之前使用application.enableevents = true
Private Sub Worksheet_Change(ByVal Target As Range)
' exit immediately on row insertion
If Target.CountLarge >= Columns.Count Then Exit Sub
If Not Intersect(Target, Columns(1)) Is Nothing Then
'escape route
On Error GoTo bm_Safe_Exit
'don't declare or Set anything until you know you will need it
'(this isn't really terribly necessary)
Dim KeyCells As Range
Set KeyCells = Range("A:A")
If Application.CountA(KeyCells) Then 'is there ANYTHING in A:A?
Application.EnableEvents = False
Call SortByDate
End If
End If
bm_Safe_Exit:
Application.EnableEvents = True
End Sub
如果不禁用事件处理并随后更改工作表上的任何内容,将触发另一个更改事件,worksheet_change事件宏将尝试在其自身之上运行。