Amend user name at start of comment

  • Thread starter Thread starter Grev
  • Start date Start date
G

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?
 
Hi Grev
AFAIK you cannot change the appearance / default content of the comment
field.

Frank
 
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
 
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)
 
Why can't you change the default settings of the comment
field. The default settings are too hard to read.
 
Back
Top