由于EXCEL中数据比较重要,所以每一次改变都需要记录下来,网上找的只是支持单个单元格改变的日志,如果要同时记录多个单元格的改变,会报错。所以将单元格改变前的值保存在集合中,这样就记录了所有单元格的值,步骤如下:
1、声明全局变量数组,用于保存改变前的值
Dim arr As New Collection
2、记录日志信息到“日志”sheet页
Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("日志")
On Error GoTo err
Dim i As Integer
ROW1 = Sheets("日志").[A65536].End(xlUp).Row + 1
i = 0
For Each ce In Target
i = i + 1
If arr(i) <> ce.Value Then
.Cells(ROW1, 1) = Now
.Cells(ROW1, 2) = Cells(ce.Row, 1)
.Cells(ROW1, 3) = arr(i)
.Cells(ROW1, 4) = ce.Value
.Cells(ROW1, 5) = ce.Address
ROW1 = ROW1 + 1
End If
Next
err:
End With
End Sub
3、将改变前的值存储在集合中,注:需要重新初始化,否则集合会一直增加下去
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set arr = New Collection
For Each tar In Target
arr.Add tar.Value
Next
End Sub