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
______________
没有评论:
发表评论