Add note to the cell

[ayman helmy]

I need a vba code to insert a note in all cells in column J when this cells changes
Noting that i want it to write the note by the value that was in the cell before change
And repeat this every time the cell change without deleting the old note

 

[saboh12617]

Hello,

If you modify cells one per one, you can do like so

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Columns("J")) Is Nothing Then Exit Sub
  UpdateCom Target
End Sub

Public Sub UpdateCom(rng As Range)
  Dim com As Comment
  Set com = rng.Comment
 
  If com Is Nothing Then
    rng.AddComment rng.Value2
  Else
    com.Text com.Text & vbCrLf & rng.Value2
  End If
End Sub

 

[Logit]

Paste in the sheet level module :

Option Explicit

Dim PreviousValue As Variant ' To store the previous value of the cell

' Track the previous value of the cell when it is selected
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Columns("J")) Is Nothing Then
        PreviousValue = Target.Value ' Store the current value before changes
    Else
        PreviousValue = vbNullString ' Clear if outside the target range
    End If
End Sub

' Add a comment with the previous value when the content is changed
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim com As Comment

    ' Ensure changes are within the desired range
    If Intersect(Target, Columns("J")) Is Nothing Then Exit Sub

    ' Remove the comment if it already exists, then re-add with the new value
    On Error Resume Next
    Set com = Target.Comment
    On Error GoTo 0

    If Not com Is Nothing Then
        com.Delete ' Remove existing comment
    End If

    ' Add a comment with the previous value
    If Not IsEmpty(PreviousValue) And PreviousValue <> "" Then
        Target.AddComment "Previous Value: " & PreviousValue
    End If
End Sub