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]

OK I can do that, but I need a special character, "##", "--", ".." some king of mark, so what do you like for a double char mark?
Sergio

 

[cmyeyez]

how about two dashes "--"

 

[sergioMabres]

Cmyeyez,
I changed the macro to workbook scope so now it works on all sheets, you can test the macro in this sheet
https://dl.dropboxusercontent.com/u/23094164/cmyeyez2.xlsm
Here is the code I used

' Adds time stamp to new or modified comments on any sheet on this workbook
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, 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
                ' Or do not delete and use cmt.Parent.Comment.Text Text:=cmtt & vbNewLine & Format(Date + Time, "YYYY-MM-DD HH:MM:SS") & mark
            End If
        Next cmt
End Sub

I hope this is what you wanted
Cheers
Sergio

 

[cmyeyez]

Greetings Sergio.

Thanks for the code -
I already have a Private Sub named "Workbook_SheetSelectionChange" that changes the background color
of the cell to "YELLOW" when a comment is added. is there a way to combine the two?

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
Range("CommentsRange" & Sh.CodeName).Interior.ColorIndex = xlNone
ThisWorkbook.Names("CommentsRange" & Sh.CodeName).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.CodeName
End If
End Sub

CAN THE ABOVE CODE BE COMBINED WITH YOUR CODE BELOW?


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, 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 ' Or do not delete and use cmt.Parent.Comment.Text Text:=cmtt & vbNewLine & Format(Date + Time, "YYYY-MM-DD HH:MM:SS") & mark End If Next cmtEnd Sub</pre>

 

[sergioMabres]

yes, both sub can be combined in one sub, sure
Sergio

 

[cmyeyez]

I'm getting a VB error saying there are duplicate Sub Names "Private Sub Workbook_SheetSelectionChange"

 

[sergioMabres]

sure, you have to put both sub code in one only one sub with all the code

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

your code

my code

end sub

But you can have incompatibilities in variable names for instance

Sergio

 

[cmyeyez]

your new code does not get rid of the Music Notes? does there have to be a "MARK" in the code at all?
can it be removed all together? don't need to see Music Notes after the comments.

Thanks.

 

[sergioMabres]

I do not wanto to get into what you describe as musical notes, they are new line and line feed character, some thing is different in your excel than mine but I do not have the time to guess what it is, so I will remuve the newline, here is the code

' Adds time stamp to new or modified comments on any sheet on this workbook
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim mark, cmtt As String
    Dim cmt As Comment
    mark = " " & "--" & " "
        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

Now you will see no newline characters, or musical notes
Mark is needed to not add timestamps to comments that already have timestamps
cheers
Sergio

 

[cmyeyez]

Hey Tom.
Thanks for this code.. can it be modified so that the comments can be entered into
ANY cell in the entire workbook? and also highlight the background to "YELLOW" OR (cmt.Parent.Interior.ColorIndex = 6) ?

Many thanks.