Change Name in Excel Comments

When you add a comment to an Excel worksheet, your name is added at the top of the comment. Your name also appears in the status bar, when you point to the cell that contains the comment.

CommentName01

It’s nice to be recognized, but sometimes you want to change the name that appears. For example, maybe you were working on the file for a client, and now you want to put the client’s name in the comments, instead of your own.

Keep reading, to see how you can quickly change the names with a macro, and see how that macro works.

How the Replace Comment Name Macro Works

In this sample file, there is a macro that replaces the old comment names, with a new name.

First, the macro prompts you for the old name – by default, the current User Name is entered.

CommentName02

Next, you’ll enter the new name for the comments. You can’t leave this blank – enter at least one character, even if it’s a space character.

CommentName03

The replaces the current User Name with the New Name. Then, it deletes each existing comment, and replaces it with a new comment. That changes the name in the status bar.

When the new comment is inserted, the Old Name at the top is replaced with the New Name.

At the end of the macro, you can decide if you want to keep the new name as the User Name for Microsoft Office.

  • Click No, if you want to keep your current user name. In this example, I want to replace the comment names in a file for my client. At the end of the macro I’d click No, because I don’t want to change the User Name on my computer – just the comment names.
  • If you want to keep the New Name as the User Name, click Yes.

CommentName04

Now, when you point to a cell with a comment, the New Name appears in both the comment, and the status bar.

CommentName05

Excel VBA Code to Replace Comment Name

To quickly change the name that appears in the status bar and the comment, you can use the following Excel VBA code.

If you’re not sure what comment names are in the workbook, you can create a list of Excel comments, with their details. Then, run this Change Comment Name code – more than once, if there are multiple old names.

For more comment programming examples, see Excel Comments VBA.

Sub ChangeCommentName()
'downloaded from www.contextures.com
'replaces old names in comments
'deletes and reinserts comments,
'so new name appears in status bar
Dim ws As Worksheet
Dim cmt As Comment
Dim cmt2 As Comment
Dim strOld As String
Dim strNew As String
Dim strUser As String
Dim strComment As String
Dim strMsg As String
Dim lBreak As Long
Dim bUser As Boolean

On Error GoTo errHandler

strUser = Application.UserName

strOld = InputBox("Old Name", "Replace Comment Name", strUser)
If Len(strOld) = 0 Then
  strMsg = "Cannot change comment names" _
    & vbCrLf _
    & "Old name must be at least one character"
  GoTo exitHandler
End If

strNew = InputBox("New Name (at least one character)", "Replace Comment Name", strUser)
If Len(strNew) = 0 Then
  strMsg = "Cannot change comment names" _
    & vbCrLf _
    & "New name must be at least one character"
  GoTo exitHandler
End If

Application.UserName = strNew
strMsg = "Could not change comments"

For Each ws In ActiveWorkbook.Worksheets
  For Each cmt In ws.Comments
    strComment = Replace(cmt.Text, strOld, strNew)
    cmt.Delete
    Set cmt2 = cmt.Parent.AddComment
    cmt2.Text Text:=strComment

    lBreak = InStr(1, cmt2.Text, Chr(10))
    If lBreak > 0 Then
      With cmt2.Shape.TextFrame
        .Characters.Font.bOld = False
        .Characters(1, lBreak - 1).Font.bOld = True
      End With
    End If
  Next cmt
Next ws

bUser = MsgBox("Keep New Name as User Name?", vbYesNo + vbQuestion, "Excel User Name")
If bUser <> vbYes Then
  Application.UserName = strUser
End If
strMsg = "Done!"

exitHandler:
  MsgBox strMsg
Exit Sub
  errHandler:
  Resume exitHandler

End Sub

______________

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *