Change Name in Excel Comments

From http://blog.contextures.com/

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.

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

______________