好吧,我真的没有在这里看到任何涵盖整个事情的内容,所以这是一个粗略的尝试。
它将处理单单元或多单元更新(您可以设置一定的限制,超出该限制...)
它不会处理多区域(非连续)范围更新,但可以扩展以执行此操作。
您可能还应该添加一些错误处理。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Where As String, OldValue As Variant, NewValue As Variant
Dim r As Long, c As Long
Dim rngTrack As Range
Application.EnableEvents = False
Where = Target.Address
NewValue = Target.Value
Application.Undo
OldValue = Target.Value 'get the previous values
Target.Value = NewValue
Application.EnableEvents = True
Set rngTrack = Sheets("Tracking").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
'multi-cell ranges are different from single-cell ranges
If Target.Cells.CountLarge > 1 And Target.Cells.CountLarge < 1000 Then
'multi-cell: treat as arrays
For r = 1 To UBound(OldValue, 1)
For c = 1 To UBound(OldValue, 2)
If OldValue(r, c) <> NewValue(r, c) Then
rngTrack.Resize(1, 3).Value = _
Array(Target.Cells(r, c).Address, OldValue(r, c), NewValue(r, c))
Set rngTrack = rngTrack.Offset(1, 0)
End If
Next c
Next r
Else
'single-cell: not an array
If OldValue <> NewValue Then
rngTrack.Resize(1, 3).Value = _
Array(Target.Cells(r, c).Address, OldValue, NewValue)
Set rngTrack = rngTrack.Offset(1, 0)
End If
End If
End Sub
获取先前值的“撤消”部分来自加里学生的回答:使用 VBA 如何检测工作表中的任何值何时发生变化? https://stackoverflow.com/questions/30513655/using-vba-how-do-i-detect-when-any-value-in-a-worksheet-changes?rq=1