Auto Comment When a cell value is being changed

[Shinod]

Hi, Can someone help me to correct this VBA?

I was looking for a VBA which gives auto comment when I change a value in any cell. I got a code that is more similar to my expectation.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    val_before = Target.Value

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then
        MsgBox Target.Count & " cells were changed!"
        Exit Sub
    End If

    If Target.Comment Is Nothing Then
        Target.AddComment
        existingcomment = ""
    Else
        existingcomment = Target.Comment.Text & vbLf & vbLf
    End If

    Target.Comment.Text Text:=Format(Now(), "DD.MM.YYYY hh:mm") & ":" & vbLf & Environ("UserName") & _
        " changed " & val_before & Target.Address & " from:" & vbLf & """" & val_before & _
        """" & vbLf & "to:" & vbLf & """" & Target.Value & """"

End Sub

This code is working. But in the comment, it is not showing what was the previous value.

Also, I want multiple lines of comment if the amendment was done multiple times. Also is it possible to change the default comment box size?

I added this VBA by right-clicking the sheet name>View code>and pasting this code there? Is it the correct way to do it?


File is attached here.

Payment Detail (12).xlsm

Microsoft Excel Workbook

1drv.ms

Thanks in Advance

 

[davesexcel]

You need the global variables first so you can pass them to the other sub.

Dim val_before
Dim comment_before As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 8 Then
    comment_before = ""
        val_before = Target.Value
        Dim c As Comment
        With Target
            On Error Resume Next
            Set c = .Comment
            On Error GoTo 0
            If c Is Nothing Then
            Else
                comment_before = c.Text
            End If
        End With
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    If Target.Column = 8 Then

        If Target.Comment Is Nothing Then
            Target.AddComment
            existingcomment = ""
        Else
            existingcomment = Target.Comment.Text & vbLf & vbLf
        End If
        Application.EnableEvents = False
        Target.Comment.Text Text:=comment_before & vbNewLine & _
                                   Format(Now(), "DD.MM.YYYY hh:mm") & ":" & Environ("UserName") & _
                                   " changed " & Target.Address & " from: " & """" & val_before & _
                                   """" & " to: " & """" & Target.Value & """"
        Target.Comment.Shape.TextFrame.AutoSize = True
        Application.EnableEvents = True
    End If
End Sub

 

[bebo021999]

Use only single sub for "change event"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vOld, vNew, cNew As String, cOld As String, c
    If Intersect(Target, Columns("H")) Is Nothing Then Exit Sub
    If Target.Count > 1 Then
            MsgBox Target.Count & " cells were changed!"
            Exit Sub
    End If
    vNew = Target.Value ' save the new value
    
    'use undo method to get old value before change
    Application.EnableEvents = False
    Application.Undo
    vOld = Target.Value ' get old value
    Target.Value = vNew 'reset target value to new value
    Application.EnableEvents = True
    
    'generate new comment. I re-arrange the text to see it clearer. adjust it if you want
    cNew = Format(Now(), "DD.MM.YYYY hh:mm") & ": " & Target.Address & " changed from: " & vOld & _
                " to: " & Target.Value & " by : " & Environ("UserName")
                
    'combine old and new comment
    Set c = Target.Comment
    If Not c Is Nothing Then
        cOld = Target.Comment.Text ' save old comment
        Target.Comment.Delete ' delete old comment
        cNew = cOld & vbLf & cNew ' combine old & new comment
    End If
    
    ' create new final comment and auto fit
    Target.AddComment (cNew)
    Target.Comment.Shape.TextFrame.AutoSize = True
End Sub

 

[Shinod]

Both way is workinh properly. Thank you @davesexcel & @bebo021999