vba cell value changed insert comment

[jbcarlile]

I have a worksheet with several columns. I would like to have the ability to track changes in value of column a and b, by showing the previous value of that cell as a comment with time and date. Any help would be great.

 

[AlphaFrog]

Right-click on the sheet tab that you want to track changes in columns A and B.
Select "View Code" from the pop-up menu.
Paste the code below in the VBA edit window.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim oComment As Comment, cell As Range, strPrev As String

    If Not Intersect(Target, Columns("A:B")) Is Nothing Then

        For Each cell In Intersect(Target, Columns("A:B"))

            Set oComment = Nothing
            On Error Resume Next
                Set oComment = cell.Comment
            On Error GoTo 0

            If Not oComment Is Nothing Then
                strPrev = Mid(oComment.Text, InStr(oComment.Text, "Current value: ") + 15, 999)
                oComment.Text Text:="Previous value: " & strPrev & Chr(10) & _
                                    "Changed: " & Format(Now, "mmm dd, yyyy  h:mm AM/PM") & Chr(10) & _
                                    "Current value: " & cell.Value
                                    
            ElseIf Not IsEmpty(cell) Then
                cell.AddComment
                cell.Comment.Text Text:="Previous value: Empty" & Chr(10) & _
                                        "Changed: " & Format(Now, "mmm dd, yyyy  h:mm AM/PM") & Chr(10) & _
                                        "Current value: " & cell.Value
                cell.Comment.Shape.Width = 150
                cell.Comment.Shape.Height = 35
                
            End If
        Next cell
    End If
   
End Sub

 

[tc200505]

Assumptions:
Comments are already in place for the cells where the values are being stored.
1. Select the cell where the value is going to be entered.
2. Run the code shown below to update the comment with the current value, date and time:

Sub UpdateComment()
    Dim str As String
    Dim str1 As String
    
    'will take the current value and store it in a variable
    str = ActiveCell.Value
    'will take the previous comments and store it in a variable
    str1 = ActiveCell.Comment.Text
    
    'Concatenate both values:
    ActiveCell.Comment.Text Text:=str1 & Chr(10) & str & "     " & Now()
    
End Sub