Comment Authors

Do you mean the comment property of the document propertie or do you mean the comments which appears as a yellow field?

in last case which change do you want to do like remove?
Sub Delete_Comment_Boxes()
    Dim c As comment
    Dim sht As Worksheet
       For Each sht In ActiveWorkbook.Sheets
            For Each c In sht.Comments
               c.Delete
            Next c
        Next sht
End Sub

or remove usernames from comments?
Sub RemoveUserNameFormComment()
    Dim c As comment
    Dim sht As Worksheet
    On Error Resume Next
       For Each sht In ActiveWorkbook.Sheets
            For Each c In sht.Comments
                commtext = c.Text
                intRow = Application.Find(Chr(10), commtext, 1)
                If intRow = 0 Then
                Else
                commtext = Mid(commtext, intRow + 1, 12)
                c.Text Text:=commtext
                End If
            Next c
        Next sht
End Sub

like you see depending on what you want to change there might b a different solution

regards,
jeroen

Thanks for the quick response.

Its the actual Author of the cell comments I want to remove. I want to keep the comment itself axactly as it is.

The Author is the information that appears in the status bar when you hover over the comment.

ok in this case you could use the macro mentioned above called: RemoveUserNameFormComment() this obne removes the usernames from the comments.

to use this open VBA editor (Alt+F11) and insert a new module.
then past the code in the module and run it.

cheers,
jeroen

But your macro is only modifying the text in the comment. I need to change the Author member of the Comment object so the Status Bar text changes.

The text in the comment, including the bold auto-inserted text, must stay exactly the same. I tested your macro and it clobered the first part of the text, but didn't alter the Status bar text.

Thanks

your right about my macro which is changing the comment it self.
never took a look at the statusbar:) im not sure where it is stored. will take a look.
interesting question. although I don't see immediatly a solution for this

The text is stored in the Author member of the Comment object, have a look in the Object Browser. However it is a read-only field so the obvious solution doesn't work.

Regards

hello,

have to go now but I think next link should give you some help:

http://support.microsoft.com/default.aspx?scid=kb;en-us;135099

Thanks

Thats for document properties. I did a bit of a search and couldn't find anything before posting the question. If there's an answer, it's likely to be a hack!

Regards

Having a look at it I can't see a simple way of changing it as it is read-only. Maybe you could think about deleting the old comment and creating a new one with Range.AddComment(Text) and use code to pick up the text off the old comment to create new.

Of course if it is YOUR name you are trying to remove this will not work as the new comment will have your name on.

Using jeoren's code I'd do something like this which will edit the comment as jeroen laid out, and change the author to whoever is running the sub:

Sub ChangeAuthorOfCommentToCurrentUser()
    Dim c As Comment
    Dim sht As Worksheet
    Dim TempAddr As String
    On Error Resume Next
       For Each sht In ActiveWorkbook.Sheets
            For Each c In sht.Comments
                commtext = c.Text
                intRow = Application.Find(Chr(10), commtext, 1)
                If intRow = 0 Then
                Else
                commtext = Mid(commtext, intRow + 1, 100)
                c.Text Text:=commtext
                End If
                TempAddr = c.Parent.Address
                c.Delete
                Range(TempAddr).AddComment commtext
            Next c
        Next sht
End Sub

just a quick note before leaving.
your right about the properties but I hoped this worked also for comments. did a small try but never the less.

there is a nasty workaround and that is creating the comments again.
what to do:
1. get text of current comment
2. delete current comment
3. create new comment with text of old comment.

in this case the username defined in the option menu(tools>>options>>general) is used you might leave this blank so no author will be visible.

Sorry I cannot be of more help today.
regards,
jeroen

I'd look at deleting and re-creating as the last option. The complication is that the comments do not have the standard format. The inital bold section has been changed to the name of the cell being commented, the sizes of the boxes are variable as they have to show all text without scrolling, several different fonts, point sizes, and weights have been used, the comments have been moved to the best spot,...

I'd need code to recreate the comment EXACTLY as it currently appears.

Removing the name from Options is the least difficult bit, I've just entered a single space and that works fine.

Thanks

PS Just looking at the code, will it work correctly given that you are adding comments into the collection you are iterating over. Would we not get into an infinite loop? You know, create a comments, move to it, delete and recreate, and loop.

hi gbentley

This code will remove any comment author but unfortunately also any formatting of the comment.

Can U use this?

rgds
cs

Sub NoCommentAuthor()
ThisUser = Application.UserName
Dim thiscom As String
Dim ThisAddress As String
For Each Sheet In Sheets
For Each Comment In Sheet.Comments
thiscom = Comment.Text
ThisAddress = Comment.Parent.Address
Sheet.Range(ThisAddress).Comment.Delete
Application.UserName = " "
With Sheet.Range(ThisAddress).AddComment
.Text thiscom
End With
Next Comment
Next Sheet
Application.UserName = ThisUser
End Sub

Try this amend to the code, you need to add all the properties you want to keep the same to the lists:

Sub ChangeAuthorOfCommentToCurrentUser()
    Dim c As Comment
    Dim sht As Worksheet
    Dim TempAddr As String
    Dim TempShape1
    Dim tempshape2
    Dim tempshape3
    Dim tempshape4
    On Error Resume Next
       For Each sht In ActiveWorkbook.Sheets
            For Each c In sht.Comments
                commtext = c.Text
                intRow = Application.Find(Chr(10), commtext, 1)
                If intRow = 0 Then
                Else
                commtext = Mid(commtext, intRow + 1, 100)
                c.Text Text:=commtext
                End If
                TempAddr = c.Parent.Address
                With c.Shape
                    TempShape1 = .Height
                    tempshape2 = .Width
                    tempshape3 = .Left
                    tempshape4 = .Top
                    'you get the idea, add all the properties here
                    'under a different tempshape
                End With
                c.Delete
                Range(TempAddr).AddComment commtext
                With Range(TempAddr).Comment.Shape
                    .Height = TempShape1
                    .Width = tempshape2
                    .Left = tempshape3
                    .Top = tempshape4
                    'and set all the tempshape properties to the new
                    'comment here
                End With
            Next c
        Next sht
End Sub

Thanks for responses guys.

I can deal with the shape. The real problem is picking up the existing formatting of the text in the comment and replicating it.

Note, I DO NOT want to modify the first part of the comment text (the auto-inserted part). I want to change only one thing, the text that appears in the Status Bar. I need to retain exactly the text and formatting that is is the Comment box including the bold bit.

Thanks

Thanks Rajesh. That is exactly what I was after. I wasn't sure how to code the bit dealing with the character array.

Sadly, I had to deliver the workbook with the existing Authors an hour ago, but I'm sure I'll use the code in the future.

Regards