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!

 

[cmyeyez]

Thanks for the response Tom.
How do I get the code to run for any cell in the workbook and not just cell $A$1?

 

[Tom Urtis]

If by "any cell in the workbook" you really mean workbook and not worksheet, then follow these steps:

Step 1
Delete the code I posted from your worksheet module.

Step 2
Place the following code in your workbook module and see if it accomplishes what you are after. To easily access your workbook module, in Excel version 2003 or before, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the immediate left of the File menu option. Right-click on that icon, and left-click to select View Code. In Excel versions 2007 or after, from your worksheet press Alt+F11, then press Ctrl+R, find your workbook name in the "Project - VBAProject" left vertical pane, expand the Microsoft Excel Object folder for your workbook, right click on ThisWorkbook and left-click to select View Code. Paste the following procedure into the large white area that is the workbook module, then press Alt+Q to return to the worksheet.

Private Sub Workbook_SheetChange(ByVal Sh As Object, 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(VBA.Now, "MM/DD/YYYY at h:MM AM/PM") & Chr(10) & strNewText
.Comment.Shape.TextFrame.AutoSize = True
End With
End Sub

 

[sergioMabres]

cmyeyez,
Here is a test sheet with a sub that will do what you want, use it carefully because it has some performance issues, it test every comment in the active sheet, I did not find an event to check for comment changes, so I check them all, the test sheet is here:
https://dl.dropboxusercontent.com/u/23094164/cmyeyez2.xlsm
The code is here:

Private Sub WorkSheet_SelectionChange(ByVal Target As Range)
Dim mark, cmtt As String
Dim cmt As Comment
mark = " " & "##" & vbNewLine
    For Each cmt In ActiveSheet.Comments
        If Right(cmt.Text, Len(mark)) <> mark Then
            cmtt = cmt.Text
            cmt.Delete
            cmt.Parent.AddComment Text:=cmtt & vbNewLine & Format(Date + Time, "YYYY-MM-DD HH:MM:SS") & mark
        End If
    Next cmt
End Sub

Cheers
Sergio

 

[cmyeyez]

tom -
with the above code, when I add a comment to cell A1 there is no time stamp.
just my user name (by default) and the actual comment.

 

[cmyeyez]

Works Great Sergio!
My picky nature is wondering why the "BOLD" font was removed from the default user name in the comment?

 

[sergioMabres]

the bold is removed when I delete the comment and then I use the method AddComment with old comment plus timestamp plus the mark, I did not find the update comment method sorry
Cheers
Sergio

 

[cmyeyez]

no worries.
Very Greatful

 

[sergioMabres]

I am glad I could help
Cheers
Sergio

 

[cmyeyez]

Good day Sergio..
how do I remove the 'eighth notes' from the time stamp?

 

[cmyeyez]

also, i need this code to work in the entire workbook - not just the sheet

thanks