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.

Dim OrigName
Const CoName = "yourCompany"

Sub WBClose()
If Application.UserName = CoName Then
Application.UserName = OrigName
End if
End Sub

Sub WbOpen()
OrigName = Application.UserName
Application.UserName = CoName
End Sub

See also
http://www.contextures.com/xlcomments03.html

--
Steve

"KIM W" <(E-Mail Removed)> wrote in message
news:A90EAA3C-FB3A-44B4-9AC9-(E-Mail Removed)...
> 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

--
If this post helps click Yes
---------------
Jacob Skaria


"KIM W" wrote:

> 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.

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.

"Jacob Skaria" wrote:

> 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
>
> --
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "KIM W" wrote:
>
> > 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.