Edit Excel Comments

P

Piddilin

When I right-click and select Edit Comment, the comment sometimes appears to open elsewhere on the screen AND, often, I have to resize it to get to where I can add more comments. Then I have to travel (scroll, mouse click, etc.) back to the original cell to continue working with the worksheet. Are these two features just a reality when working with comments in Excel?
 
K

Ken Wright

The following routine from an old post of William's (Hi stranger, if you're out
there), will rewrite all your comments for you, putting them back in place
alongside their linked cells. It will move ALL comments, so be sure that's what
you want.

Sub RewriteComments()
Application.ScreenUpdating = False
Dim c As Range, s As String, r As Range
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeComments)
For Each c In r
s = c.NoteText
c.ClearComments
c.NoteText s
c.Comment.Visible = False
c.Comment.Shape.TextFrame.AutoSize = True
Next c
Application.ScreenUpdating = True
End Sub
 
G

Gord Dibben

diddlin

See Debra Dalgleish's site for more on Comments......

reset position, changing default text, font, and shapes.

Some info in basics and some in programming sections.

This autosize code is also handy.

Public Sub Comment_Size()
Dim cmt As Comment
Dim cmts As Comments
Set cmts = ActiveSheet.Comments
For Each cmt In cmts
cmt.Shape.TextFrame.AutoSize = True
Next
End Sub

Gord Dibben Excel MVP
 
Top