Change Comment.Author

  • Thread starter Thread starter KIM W
  • Start date Start date
K

KIM W

I have learned that the Comment.Author is read-only. Is there any workaround
or technique to alter the author? (This is the username name displayed at
the bottom of Excel when a comment is opened.)
We are finding that the stickiness of the name of the person who happened to
create the comment is undesirable. Now that the workbook is "done", we'd
like to loop through the hundreds of comments and make the author our company
name.
 
Kim

Try the below macro and feedback. This should replace all your comments with
the organization name. Change the organization name to suit

Sub Macro()
Dim myRange As Range, myCell As Range
Dim strTemp As String, strAuthor As String, strOrgName As String

strOrgName = "My Organization name"
For intSheet = 1 To Sheets.Count
Set myRange = Sheets(intSheet).UsedRange
For Each myCell In myRange
If Not myCell.Comment Is Nothing Then
strAuthor = myCell.Comment.Author
strTemp = myCell.Comment.Text
myCell.Comment.Delete
myCell.AddComment Replace(strTemp, strAuthor, strOrgName)
End If
Next
Next
End Sub
 
I implemented the code below-- thanks. I learned some stuff and still have
issues:
1. My objective is to alter the name displayed at the bottom of the screen
with the text "Commented By" when a comment is selected-- not the name right
in the comment (because I remove that on each comment). I discovered by
using the code below that when a comment is created it uses the User Name set
in Excel Options as the name at the bottom-- not comment.author.
Alternatively, the User Name can be set in code using Steve's suggestion of
Application.UserName. So, I can work with this-- it is basically solved,
though it is not behaving exactly as expected.
2. Most of my comments have different fonts and dimensions-- I think I can
find how to copy the font, but how about the dimensions of the comment?
3. Many of my comments have a picture embedded in them-- this code below
blows away the picture. (To insert picture which is a .jpg screen shot in
comments I use Edit Comment... Format Comment... Fill... Color... Fill
Effects... Picture... Select Picture)

The business objective here is to remove the numerous spreadsheet builders'
names from comments and "brand" the application.

Thanks in advance for additional assistance.
KIM W.
 
Back
Top