User name in comments

[hp12c]

Is it possible to change the default comment format to NOT include the username in the comment?

Thanks.

 

[Tom Urtis]

Yes it is possible. Aside from removing the user name from Excel, which in some versions requires a re-install of Office, here are two macros that you can assign to shortcut keys that will give you a default of no user name in new comments.

The first macro is bare-bones; it creates a new comment and keeps the comment box visible so you can click in it and edit it.

The second macro is heavier; it deletes the old comment if one was there, and sets the Visible property to False while putting you in edit mode to enter comment text, so it's more versatile. Some people hate invoking the SendKeys method, and that can indeed be a volatile proposition, but with some reasonable care you can use the SendKeys to your advantage, as in this case.


Sub Test1()
With ActiveCell
.AddComment Text:=""
.Font.FontStyle = "Regular"
.Comment.Visible = True
End With
End Sub

Sub Test2()
On Error Resume Next
With ActiveCell
.AddComment
.Comment.Visible = False
.Comment.Text Text:=""
End With
SendKeys "%{I}"
SendKeys "{E}"
SendKeys "{ENTER}"
End Sub

 

[Ekim]

If you want to remove the user name from pre-existing comments (macro author not known):

Option Explicit
Sub ChangeCommentsName()
Dim cmt As Variant
Dim c As Variant

     Set cmt = Worksheets("Sheet1").Comments
     For Each c In cmt
         c.Text "" & Right(c.Text, _
        Len(c.Text) - InStr(c.Text, ":") - 1)
        
     Next
End Sub

Caution:
Only run the macro once in a worksheet. After the user name is removed, the comment is moved up to the first line of the comment box. Running the macro again will start deleting characters from the actual comment.

Regards,

Mike

 

[hp12c]

Thanks!