CODEWITHSUNDEEP
vbaexcel
Ken
Ken

Reputation: 19

Using Comment to record every change in cells

I would like to use comment to record every change in cells but Excel crashes every time when the code is execute.

For the Undo part, is there any better ways for me to record the old value and new value ?

Can anyone give me some advice? Thanks 

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range
Dim ws As Worksheet

For Each r In Target

    new_value = r.Value
    Application.Undo
    old_value = r.Value
    r.Value = new_value

    If r.Value = "" And r.Comment Is Nothing Then
        r.AddComment.Text Application.UserName & " has added " & new_value & " at " & Now

    ElseIf r.Value <> "" And r.Comment Is Nothing Then
        r.AddComment.Text Application.UserName & " has changed from " & old_value & " to " & new_value & " at " & Now

    ElseIf Not r.Value = "" And r.Comment Is Nothing Then
        r.Comment.Text Application.UserName & " has changed from " & old_value & " to " & new_value & " at " & Now

    End If

Next
End Sub

Upvotes: 0

Views: 134

Answers (1)

Storax
Storax

Reputation: 12167

In order to avoid Excel running into an endless loop you need to turn off events

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r As Range
Dim ws As Worksheet

Application.EnableEvents = False

For Each r In Target

    new_value = r.Value
    Application.Undo
    old_value = r.Value
    r.Value = new_value

    If r.Value = "" And r.Comment Is Nothing Then
        r.AddComment.Text Application.UserName & " has added " & new_value & " at " & Now

    ElseIf r.Value <> "" And r.Comment Is Nothing Then
        r.AddComment.Text Application.UserName & " has changed from " & old_value & " to " & new_value & " at " & Now

    ElseIf Not r.Value = "" And r.Comment Is Nothing Then
        r.Comment.Text Application.UserName & " has changed from " & old_value & " to " & new_value & " at " & Now

    End If

Next

Application.EnableEvents = True

End Sub

Now you can check if your code is doing what you want it to do.

Upvotes: 1

Related Questions