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

 

[James006]

Hi,

Not sure to understand what is your objective whenever there is already a comment ...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oComment As Comment, cell As Range, strPrev As String
If Intersect(Target, Columns("H:K")) Is Nothing Then Exit Sub
        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
                ' There is ALREADY a Comment ''''''''''''''''''''''''''''''''''''''''''''''''''''
                ' 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 Sub

 

[pure vito]

Hi @James006 thank you very much, I think I confused myself reading my post back

I have multiple sheets like this using the same VBA in columns H:K

My Problem is when I update this workbook it means I have to transfer the data from H:K to an identical workbook the issue I have is when I copy this data and paste in into a blank copy the time stamps are all reset and the comments are missing I hope that makes sense,

 

[James006]

Hi again,

Not yet sure to totally understand your situation ... and the problem you are confronted with ...
1. Copying a cell does copy both the cell content AND the attached comment
2. If you only need the comments, you can use a dedicated instruction .PasteSpecial Paste:=xlPasteComments
3. Same instruction can be used ... if, for an unknown reason, your comments have disappeared ...
4. Is there an event macro in your destination files which would prevent your copy process from operating properly ?

 

[pure vito]

Thanks James I think this may be a complex task,

This is to help me with this thread Worksheet Data Transfer VBA as I want to attempt a complete data transfer so that would mean the contents and the comments in the cells would move over, but the VBA is not allowing me to do that without resetting the comments

 

[James006]

You are not answering the main question : Is there a worksheet Event macro in the destination file ?

My guess is "Yes"

This is the reason why as you said "VBA is not allowing me to ..."

The solution is to amend your Event macro with a Boolean variable (True/False)
so that you have it working under standard circumstances ...
BUT to Exit it whenever you are performing your Copy process ...

 

[pure vito]

Yes that is correct James sorry I misunderstood, every worksheet has the same VBA worksheet event
and you are right in your solution that makes perfect sense in what I'm trying to achieve is this something you could help with please

 

[James006]

Yes that is correct James sorry I misunderstood, every worksheet has the same VBA worksheet event

So you have to design your own solution by
amending your Event macro with a Boolean variable (True/False)
so that you have the Event macro working under standard circumstances ...
BUT to Exit the Event macro whenever you are performing your Copy process ...

 

[pure vito]

That is correct James I just don't know how implement that to the code is that something you can help with please

 

[James006]

Below a simple example ... for your inspiration

Private Sub Worksheet_Change(ByVal Target As Range)
' Create a Variable blCopy
Dim blCopy As Boolean

' You have to decide how you modify its value : TRUE or FALSE
' For example, if you add 0 in cell A1, you want to Exit the Event Macro :
   If Range("A1") = 0 Then
      blCopy = True
   Else
      blCopy = False
   End If
  
' Your instruction to Exit the Event Macro
If blCopy = True Then Exit Sub

' If you do not Exit ....

' Insert Your Current Macro which will operate as usual ...

End Sub