Autotext in comment

[Guest]

My question is short and sweet. Is there any way to have the current date
automatically appear when inserting a comment. Obviously Excel puts the
current user....I'd like to get rid of that and have the date instead. Any
assistance you could give would be greatly appreciated.

Thanks
Eric

 

[Jason Morin]

You could try a macro like this and assign it to a button
on a toolbar:

Sub NewComment()
Dim commTxt As String
commTxt = InputBox("Enter comment text: ")
If commTxt = "" Then Exit Sub
With ActiveCell
.AddComment
.Comment.Text Text:=Format(Now(), "mm/dd/yy") & _
Chr(10) & commTxt
.Comment.Visible = False
End With
End Sub

 

[Debra Dalgleish]

There's no setting you can change to automatically insert the current
date at the top of a comment.

You could create a macro that inserts a comment with the date. Store the
macro in a workbook that's always open, such as Personal.xls. Then, add
a shortcut key, or a toolbar button to run the macro.

For example:

Sub CommentWithDate()
'adds date at top of comment
Dim cmt As Comment
Set cmt = ActiveCell.Comment
If cmt Is Nothing Then
ActiveCell.AddComment Text:=Format(Date, "Medium Date") & Chr(10)
Set cmt = ActiveCell.Comment
With cmt.Shape.TextFrame.Characters.Font
.Name = "Times New Roman"
.Size = 11
.Bold = False
.ColorIndex = 0
End With
End If
SendKeys "%ie~"
End Sub