Editing Comments (any)

Topic

New Reply

I have a spreadsheet with many comments thoughout, and suddenly I cannot edit some of the comments. Only a few, the other I can edit. What would cause these few to not allow me to edit them? When I right-click on the cell and select edit comment, nothing happens. Please help… Thanks.

-tmg9671

Reply | Quote

Replies

Could you attach a small workbook that demonstrates the problem?

Reply | Quote

See attached. Comment 1 can be edited, but Comment 2 cannot. Thanks.

Reply | Quote

That’s weird! I can edit comment 2 by selecting its cell, choosing Show comment, moving the cursor off the cell and then back over it.
Did you create them in different ways?

Reply | Quote

That is weird. I tried that too and it worked. I didn’t create them any differently, other than perhaps copying and pasting the comment into a different cell.

At least I can still edit them. Thanks for your help.

Reply | Quote

This is strange – I cannot explain why it happens. The problem is mentioned in the Micorosft newsgroups, but without a clear solution.

If you set the display option for comments to ‘Comment and indicator’, it is also possible to select the cell, then click in the comment to edit it.

Reply | Quote

When I select “show comment” from the context menu, I am able to edit Comment 2…as opposed to selecting Edit Comment from the context menu…

Odd problem.

Reply | Quote

Yes, that is what Rory mentioned in his reply. It’s definitely a weird problem – I cannot find any relevant difference in the properties of the two cells and their comments that would explain why Edit Comment works for one, but not for the other.

Reply | Quote

Well, at least there is a way to edit them. Thank you all for your help.

Reply | Quote

Try running this macro – it will basically delete and replace all your comments with “fresh ones” which seemed to help on your sample workbook:

Sub CleanComments()
    Dim cmt As Comment
    Dim strComment As String
    Dim rngCell As Range
    Dim sglHeight As Single, sglWidth As Single
    For Each cmt In ActiveSheet.Comments
        With cmt
            sglHeight = .Shape.Height
            sglWidth = .Shape.Width
            Set rngCell = .Parent
            strComment = .Text
            .Delete
        End With
        With rngCell
            .AddComment strComment
            With .Comment.Shape
                .Height = sglHeight
                .Width = sglWidth
                .TextFrame.Characters.Font.Bold = True
            End With
            .Comment.Visible = False
        End With
    Next cmt
End Sub

HTH

Reply | Quote