Excel自动添加带有单元格编辑历史记录的注释 [英] Excel automatically add comment with cell edit history

问题描述

我在工作表宏"中有以下代码(右键单击工作表-查看代码).它曾经可以工作，但是现在它不在我指定的范围A5:AQ155中添加注释.

I have the following code in the "sheet macros" (right click sheet - view code). It used to work but now it's not adding comments in my specified range A5:AQ155.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

'If (Target.Row > 3 And Target.Row < 155) Then Cells(Target.Row, "AT") = Now()

Const sRng As String = "A5:AQ155" ' change as required
Dim sOld As String
Dim sNew As String
Dim sCmt As String
Dim iLen As Long
Dim bHasComment As Boolean


With Target(1)
If Intersect(.Cells, Range(sRng)) Is Nothing Then Exit Sub
sNew = .Text
sOld = .Text
.Value = sNew
Application.EnableEvents = True


sCmt = "Edit: " & Format$(Now, "dd Mmm YYYY hh:nn:ss") & " by " & Application.UserName & Chr(10) & "Previous Text :- " & sOld


If Target(1).Comment Is Nothing Then
.AddComment
Else
iLen = Len(.Comment.Shape.TextFrame.Characters.Text)
End If


With .Comment.Shape.TextFrame
.AutoSize = True
.Characters(Start:=iLen + 1).Insert IIf(iLen, vbLf, "") & sCmt
End With
End With
End Sub

我做错了什么?

推荐答案

代码已停止触发，因为事件触发已被禁用并且从未重新打开.编写代码的方式是，一旦有人在范围 A5:AQ155 之外的地方对工作表进行了更改，则事件将被禁用而不重新打开，这意味着不会触发后续的事件触发器(即-下次您编辑单元格).

The code stopped firing because Event Firing was disabled and never turned back on. The way the code is written, as soon as someone makes a change to the worksheet outside the range A5:AQ155, the Events become disabled without being turned back on, which means subsequent event triggers will not be fired (ie. - the next time you edit a cell).

如果您在代码中进行了这些细微调整，则应该可以按预期进行工作.

If you make these slight tweaks in the code it should work as intended going forward.

但是，在执行此操作之前，请在立即窗口中键入 Application.EnableEvents = True ，然后按Enter键重新打开事件，以便代码再次开始触发.

However, before you do this type Application.EnableEvents = True in the immediate window and hit Enter to turn events back on so that the code begins to fire again.

Private Sub Worksheet_Change(ByVal Target As Range)

Const sRng As String = "A5:AQ155" ' change as required
Dim sOld As String
Dim sNew As String
Dim sCmt As String
Dim iLen As Long

If Not Intersect(Target, Me.Range(sRng)) Is Nothing Then

    Application.EnableEvents = False

    With Target

        sNew = .Value2
        Application.Undo
        sOld = .Value2
        .Value2 = sNew

        Application.EnableEvents = True

        sCmt = "Edit: " & Format$(Now, "dd Mmm YYYY hh:nn:ss") & " by " & Application.UserName & Chr(10) & "Previous Text :- " & sOld


        If .Comment Is Nothing Then
            .AddComment
        Else
            iLen = Len(.Comment.Shape.TextFrame.Characters.Text)
        End If

        With .Comment.Shape.TextFrame
            .AutoSize = True
            .Characters(Start:=iLen + 1).Insert IIf(iLen, vbLf, "") & sCmt
        End With

    End With

End If

End Sub

这篇关于Excel自动添加带有单元格编辑历史记录的注释的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！