|
由于表格需要每周更新数据,需要在更新后跟踪更改而不共享工作簿(共享工作簿其他工作表的宏不能启用),就写了下面的这个事件,当更改单元格时,会显示批注解释单元格做了什么更改。
问题:这个事件只能一个单元格一个单元格输入来触发事件,无法批量复制粘贴来同时每个单元格都触发事件,请问怎么修改才能实现可复制粘贴并且每个单元格都能触发此事件呢?
效果
Private Sub Worksheet_Change(ByVal Target As Range)
Const xRg As String = "A1:ZZ1000"
Dim strOld As String
Dim strNew As String
Dim strCmt As String
Dim xLen As Long
With Target(1)
If Intersect(.Cells, Range(xRg)) Is Nothing Then Exit Sub
strNew = .Text
Application.EnableEvents = False
Application.Undo
strOld = .Text
.Value = strNew
Application.EnableEvents = True
strCmt = "Edit: " & Format$(Now, "dd Mmm YYYY hh:nn:ss") & " by " & _
Application.UserName & Chr(10) & "Previous Text :- " & strOld
If Target(1).Comment Is Nothing Then
.AddComment
Else
xLen = Len(.Comment.Shape.TextFrame.Characters.Text)
End If
With .Comment.Shape.TextFrame
.AutoSize = True
.Characters(Start:=xLen + 1).Insert IIf(xLen, vbLf, "") & strCmt
End With
End With
End Sub
|
|