Exel 2007-2010 - auto-insert date when add new or edit comment

Question

I see that when I add new a comment to a cell, Excel auto insert my username into it. So I wonder whether or not we can configure the way Excel add header to my comment.
Actually, I need Excel to auto-insert date when I insert a new comment. Can I do it? If yes, can you give some guide to do it!
Thanks!

---

FYI, I'm trying with some pieces of VBA code, but still can't get anything helpful! Maybe next weekend... humph

Answer 1

There isn't any easy way to do this. If it is a one-time thing, you can go into Excel Options and add the date to your user name. You would have to remember to undo the change when done.

Other than that you would have to write some code. Unfortunately, Excel doesn't register an event when you create or edit a comment so you can't edit a comment as it is being created. The best you can do is write a routine to handle the adding of comments. You would then need to always use the macro for adding comments.

Here is a quick example:

Sub AddNewComment()
   Dim sComment As String, rng As Range

   If TypeName(Selection) = "Range" Then
      Set rng = Selection

      sComment = InputBox("Enter your comment.", "Add New Comment")
      If Len(sComment) > 0 Then
         ' append date and username to comment
         sComment = Format(Date, "m/d/yy") & " " & Application.UserName & ":" & vbLf & sComment
         If rng.Comment Is Nothing Then
            rng.AddComment sComment
         Else
            ' append new comment to existing comment
            rng.Comment.Text vbLf & sComment, Len(rng.Comment.Text) + 1, False
         End If

      End If

      Set rng = Nothing
   End If

End Sub

Note: All text will be plain. You can't set part of your text (UserName) as bold with the .Text function.