Amend user name at start of comment

[Grev]

Is it possible to amend the default user name at the start
of a comment so that it also includes the date the comment
was created?

 

[Frank Kabel]

Hi Grev
AFAIK you cannot change the appearance / default content of the comment
field.

Frank

 

[JMay]

Put In a standard module:

Sub CommentNameDate()
'adds comment with name and date
Dim cmt As Comment
Set cmt = ActiveCell.Comment
If cmt Is Nothing Then
ActiveCell.AddComment _
Text:=Application.UserName & Chr(10) _
& Format(Date, "yyyy-mm-dd") & 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

It would be easiest to assign the above to a new toolbar icon.
HTH

 

[Bob Phillips]

I like this a lot, but rather than a button as JMay suggests, I would
suggest changing the right-click menu option for 'Insert Comment' to use
this code. This code hides the 'Insert Comment' menu item, and adds a new
'Insert Comment+' item, that calls the new code

Sub addEnhancedComment()
Dim oCtl As Office.CommandBarControl
Dim iPos As Long
With Application.CommandBars("cell")
On Error Resume Next
.Controls("Insert Comment+").Delete
On Error GoTo 0
Set oCtl = Application.CommandBars.FindControl(ID:=2031)
iPos = oCtl.Index
.Controls("Insert Comment").Visible = False
Set oCtl = .Controls.Add(before:=iPos)
With oCtl
.BeginGroup = True
.Caption = "Insert Comment+"
.FaceId = 2031
.OnAction = "'" & ThisWorkbook.Name & "'!EnhancedComment"
End With
End With
End Sub

Note that I renamed the macro to EnhancedComment

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Why can't you change the default settings of the comment
field. The default settings are too hard to read.

 

[Grev]

Thanks JMay

 

[Frank Kabel]

Hi
have a look at the really nice solutions from JMay and Bob
Frank