VBA Timestamp Note Based on Cell Input

[pure vito]

Hi Everyone,

I have a VBA code here that when data is entered into a cell within the range,
It automatically adds a note with a timestamp the only issue I have here is that if data is pasted into the cells or changed it resets the note with a new timestamp,
Is their anything that can be done to change this? and allow for old notes with comments to be pasted into these cells with out it resetting and removing comments in the note?

Thanks in advance,

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

    If Not Intersect(Target, Columns("H:K")) Is Nothing Then

        For Each cell In Intersect(Target, Columns("H:K"))

            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:=Format(Now, "mmm dd, yyyy  h:mm AM/PM")
                                   
                                   
            ElseIf Not IsEmpty(cell) Then
                cell.AddComment
                cell.Comment.Text Text:=Format(Now, "mmm dd, yyyy  h:mm AM/PM")
                cell.Comment.Shape.Width = 150
                cell.Comment.Shape.Height = 35
               
            End If
        Next cell
    End If
   
End Sub

 

[pure vito]

Thank you James i'll see if I can work with this, and if you have the time i'd really appreciate if you could offer advise on the other thread I mentioned, thanks again

 

[pure vito]

Hi All can anyone help with this please I'm thankful for the help given here, but after trying to make this change myself I'm failing and lacking the skill to complete

 

[James006]

Hi,
You can adapt the following

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

' Adjust the cell to your own situation
' You will use cell Z1 as a Falg to say Yes or No
' With 0 Nothing will happen
' If you type 1 in cell Z1, then this macro will operate as before

If Range("Z1").Value = 0 Then Exit Sub

    If Not Intersect(Target, Columns("H:K")) Is Nothing Then
        For Each cell In Intersect(Target, Columns("H:K"))
            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:=Format(Now, "mmm dd, yyyy  h:mm AM/PM")
            ElseIf Not IsEmpty(cell) Then
                cell.AddComment
                cell.Comment.Text Text:=Format(Now, "mmm dd, yyyy  h:mm AM/PM")
                cell.Comment.Shape.Width = 150
                cell.Comment.Shape.Height = 35
            End If
        Next cell
    End If
End Sub

 

[pure vito]

That's awesome thank you James I really did try, with this now I can perform my data transfer and after create a Macro to insert 1 into Z1 of every sheet meaning all the notes will be transferred and then the will continue to work as normal, that's great thank you very much

 

[James006]

You are welcome