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!

 

[sergioMabres]

Here is a function that will add time stamp when cell is changed

Function whenChanged(pCell As Object) As Date
    wMod = "Changed"
    wSep = ";"
    wDate = Date + Time
    wtext = pCell.Text
    wAd = pCell.Address
    wWs = pCell.Worksheet.Name
    If Worksheets(wWs).Range(wAd).Comment Is Nothing Then
        Worksheets(wWs).Range(wAd).AddComment wSep & wSep
    End If
    wCom = Worksheets(wWs).Range(wAd).Comment.Text
    wArr = Split(wCom, wSep)
    If wArr(0) = "" Then
        wNCom = wMod + wSep + Format(wDate, "YYYY-MM-DD HH:MM:SS") + wSep + wtext
        Worksheets(wWs).Range(wAd).Comment.Visible = False
        Worksheets(wWs).Range(wAd).Comment.Text wNCom
        whenChanged = wDate
    Else
        If wArr(0) = wMod Then
            If wtext = wArr(2) Then
                whenChanged = CDate(wArr(1))
            Else
                wNCom = wMod + wSep + Format(wDate, "YYYY-MM-DD HH:MM:SS") + wSep + wtext
                Worksheets(wWs).Range(wAd).Comment.Visible = False
                Worksheets(wWs).Range(wAd).Comment.Text wNCom
                whenChanged = wDate
            End If
        Else
            MsgBox "You are about to erase a comment in cell " & wAd & ", you can not use function whenChanged here."
            whenChanged = 0
        End If
    End If
End Function

I hope this is close to what you need
Cheers
Sergio

 

[cmyeyez]

THANKS Sergio!
will try the function out and let you know

 

[cmyeyez]

Greetings Sergio!

Im not sure I'm calling the function correctly. I also have code that highlights the cell to "Yellow" when a 'comment'
is added to the cell. The highlight code works but for some reason the function isn't"
__________________________________________________________________________
Private Sub Workbook_Open()
CreateNamesForCommentsOnEachSheet
End Sub
Private Sub CallDateTimeFunction()
whenChanged
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Range("CommentsRange" & Sh.Name).Interior.ColorIndex = xlNone
ThisWorkbook.Names("CommentsRange" & Sh.Name).Delete
On Error GoTo 0
Set ThisSheetComments = Nothing
If Sh.Comments.Count > 0 Then
For Each cmt In Sh.Comments
cmt.Parent.Interior.ColorIndex = 6
If ThisSheetComments Is Nothing Then Set ThisSheetComments = cmt.Parent Else Set ThisSheetComments = Union(ThisSheetComments, cmt.Parent)
Next cmt
ThisSheetComments.Name = "CommentsRange" & Sh.Name
End If
End Sub
Sub CreateNamesForCommentsOnEachSheet()
For Each sht In ThisWorkbook.Worksheets
Set xxx = sht.Comments
If xxx.Count > 0 Then
Set ThisSheetComments = Nothing
For Each cmt In xxx
If ThisSheetComments Is Nothing Then Set ThisSheetComments = cmt.Parent Else Set ThisSheetComments = Union(ThisSheetComments, cmt.Parent)
Next cmt

End If
Next sht
For Each nm In ThisWorkbook.Names
If Left(nm.Name, 13) = "CommentsRange" Then
Set zzz = Nothing
On Error Resume Next
Set zzz = Range(nm.Name)
On Error GoTo 0
If zzz Is Nothing Then nm.Delete
End If
Next nm
End Sub

Function whenChanged(pCell As Object) As Date
wMod = "Changed"
wSep = ";"
wDate = Date + Time
wtext = pCell.Text
wAd = pCell.Address
wWs = pCell.Worksheet.Name
If Worksheets(wWs).Range(wAd).Comment Is Nothing Then
Worksheets(wWs).Range(wAd).AddComment wSep & wSep
End If
wCom = Worksheets(wWs).Range(wAd).Comment.Text
wArr = Split(wCom, wSep)
If wArr(0) = "" Then
wNCom = wMod + wSep + Format(wDate, "YYYY-MM-DD HH:MM:SS") + wSep + wtext
Worksheets(wWs).Range(wAd).Comment.Visible = False
Worksheets(wWs).Range(wAd).Comment.Text wNCom
whenChanged = wDate
Else
If wArr(0) = wMod Then
If wtext = wArr(2) Then
whenChanged = CDate(wArr(1))
Else
wNCom = wMod + wSep + Format(wDate, "YYYY-MM-DD HH:MM:SS") + wSep + wtext
Worksheets(wWs).Range(wAd).Comment.Visible = False
Worksheets(wWs).Range(wAd).Comment.Text wNCom
whenChanged = wDate
End If
Else
MsgBox "You are about to erase a comment in cell " & wAd & ", you can not use function whenChanged here."
whenChanged = 0
End If
End If
End Function

 

[sergioMabres]

please let me show you how to use the function, see sample sheet at
https://dl.dropboxusercontent.com/u/23094164/cmyeyez1.xlsm
Do you get the drift?
Is an idea how to do a time stamp in a comment
Cheers
Sergio

 

[cmyeyez]

thanks Sergio.
but when i place a new comment into a cell in the worksheet,
there is no date/time stamp in the comment.

 

[cmyeyez]

I would like the date/time stamp to be there immediately after
the comment is entered and the user clicks out of the cell

 

[sergioMabres]

I see so you need a custom function
bye
Sergio

 

[cmyeyez]

so that will be 50cents?

 

[Tom Urtis]

Might this approach help you?

Suppose you want your employees to enter an explanation or description of some kind into a cell regarding a topic on your spreadsheet. Maybe there’s a new product being developed and you’ll utilize cell A1 for the team members to enter their ideas during production.
You want to keep a record of everything entered, without burdening anyone with how to edit existing text or how to add a new comment to a cell. The simpler, the better.

In the picture, over the course of time, new entries are made into cell A1. Although each new entry in A1 overrides pre-existing text, the below code will capture all the text ever entered. There’s also a date and time stamp for each new entry, and an empty line between entries for readability.

To install the code, right-click on your worksheet tab, left-click to select View Code, and paste the following procedure into the large white area that is the worksheet module. Then press Alt+Q to return to your worksheet, and test the code to see how the comment creates and updates itself with every change in text you make to cell A1.

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