editing comments in MS excel 07

[krisenthia]

i have a spreadsheet with like 400 rows of info. I have inserted comments on
several fields, and when come back later and edit a comment, the comment is
linked far far away from the actual field i was in to begin with. for
example, I'll be editing a comment in cell a10, and the comment itself will
be linked all the way down to a367. Is this a bug that cannot be fixed and I
just have to deal with, or is there something I can quickly do to tell Excel
to anchor the comments to the cell, especially in edit mode? BTW, I have like
200 inserted comments.

 

[Jim Rech]

You could try this macro to reposition all comments near their cells.

Sub RestoreComments()
Dim AllCommentCells As Range
Dim Cell As Range
Dim LastCol As Integer
LastCol = ActiveSheet.Columns.Count
Set AllCommentCells = Cells.SpecialCells(xlCellTypeComments)
For Each Cell In AllCommentCells
With Cell.Comment.Shape
.Width = 96
.Height = 55.5
.Top = Cell.Top + 5
If Cell.Column < (LastCol - 3) Then
.Left = Cell.Offset(0, 1).Left + 10
Else
.Left = Cell.Offset(0, (LastCol - Cell.Column) - 3).Left
End If
End With
Next
End Sub


--
Jim
|i have a spreadsheet with like 400 rows of info. I have inserted comments
on
| several fields, and when come back later and edit a comment, the comment
is
| linked far far away from the actual field i was in to begin with. for
| example, I'll be editing a comment in cell a10, and the comment itself
will
| be linked all the way down to a367. Is this a bug that cannot be fixed
and I
| just have to deal with, or is there something I can quickly do to tell
Excel
| to anchor the comments to the cell, especially in edit mode? BTW, I have
like
| 200 inserted comments.