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.
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.
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.
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.
Now, when you point to a cell with a comment, the New Name appears in both the comment, and the status bar.
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