DATE/TIME Stamp for "COMMENTS"

[cmyeyez]

does excel have an auto- date/time stamp that can be placed into the comments?
(similar to how the UserName auto populates in the comment based on who the excel software is registered under)
I would need this to track the date/time a comment was added to a cell.

Many thanks!

 

[Khaled040]

Tom Urtis Hey Tom, I tried to use your code in post#10 but I just deleted this line "If .Address <> "$A$1" Then Exit Sub" so it can work in the whole sheet, my question is, how can I add the user name who made the changes too ? i need to get the updates with username and timestamps ?
Thanks in advance

 

[tharveen]

Good Discussion guys...

However being a newbie, i think i am doing something wrong in adding this code.

Could someone guide by steps how to do this, please.

Thank you.

 

[stimey89]

Hi Tom,
I have a couple of questions regarding the VB code you posted.

1 - Can the code be modified to capture the changed text if the text is being copied from another cell rather than typed in?
2 - Can the date be displayed in another cell as well as a comment attached to the changed cell?
3 - If the date can be displayed in another cell, can that date be overwritten each time the triggering cell is modified.

Thanks for your help.

 

[minimalist]

Hi Tom,
Thank you, this works great! What if I wanted to add these comments only to a certain range of a certain worksheet, for example Sheet1!K3:K300 or the entire column K?

BTW, in order to add the username in front of the date and time in the comment, I changed the 4th to last line in your code to:

Format(Application.UserName & " " & VBA.Now, "MM/DD/YYYY at h:MM AM/PM") & ": " & Chr(10) & strNewText

Thanks!

 

[EBoudreau]

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address <> "$A$1" Then Exit Sub
If IsEmpty(Target) Then Exit Sub
Dim strNewText$, strCommentOld$, strCommentNew$
strNewText = .Text
If Not .Comment Is Nothing Then
strCommentOld = .Comment.Text & Chr(10) & Chr(10)
Else
strCommentOld = ""
End If
On Error Resume Next
.Comment.Delete
Err.Clear
.AddComment
.Comment.Visible = False
.Comment.Text Text:=strCommentOld & _
Format(VBA.Now, "MM/DD/YYYY at h:MM AM/PM") & Chr(10) & strNewText
.Comment.Shape.TextFrame.AutoSize = True
End With
End Sub

I am interested in doing the same thing with this by adding date and time to every comment added to a couple sheets in the workbook.

I have existing code in both sheets so how would I add this code to existing code? I've tried it a few times and I cannot get it to work.

Any help would be greatly appreciated.

 

[johnrm1]

Just to update all - to enable auto comment with user name and time stamp for the entire sheet - use the below code. I don't know how to make it specific for a range of cells or column, so please feel free to comment if you do:

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If IsEmpty(Target) Then Exit Sub
Dim strNewText$, strCommentOld$, strCommentNew$
strNewText = .Text
If Not .Comment Is Nothing Then
strCommentOld = .Comment.Text & Chr(10) & Chr(10)
Else
strCommentOld = ""
End If
On Error Resume Next
.Comment.Delete
Err.Clear
.AddComment
.Comment.Visible = False
.Comment.Text Text:=strCommentOld & _
Format(Application.UserName & " " & VBA.Now, "MM/DD/YYYY at h:MM AM/PM") & ": " & Chr(10) & strNewText
.Comment.Shape.TextFrame.AutoSize = True
End With
End Sub

 

[prephil]

I have tried unsuccessfully to follow several procedures in this thread. I am following Toms procedure shown below but can't get it to work on Office 2016.

I am following the steps below.

After the above step I press Alt + Q and then insert a comment but only get my name, no time and date stamp.
Can anyone point out what I'm doing wrong?