problem with Excel 2007 comments

[hsclater]

I have 2 problems with comments in Excel 2007.

1. Sometimes after I come back to a comment and choose edit, the edit
box is so far away from the actual cell I have to scroll around
following the line that connects to it - sometimes miles across the
spreadsheet in the middle of nowhere. Why does the comment box move so
much? It displays in the right place if you hover the mouse over it.

2. Sometimes I edit a comment I have put there myself by right
clicking and choosing Edit Comment. Nothing happens. If I right click
and choose Show Comment, then hover, it will pop up, but again often
far away from the cell.

Any ideas why? It makes working with comments very annoying....

Thanks

 

[Jim Rech]

Re #1 I think the comments' Move And Size with Cells setting gets turned on
somehow. After that comments move and resize when columns and rows are
inserted, etc.

You could run this macro to restore all comments to their default position
and size..

Sub RestoreComments()
Dim AllCommentCells As Range
Dim Cell As Range
Dim LastCol As Integer
LastCol = ActiveSheet.Columns.Count
On Error GoTo NoComments
Set AllCommentCells = Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0
For Each Cell In AllCommentCells
With Cell.Comment.Shape
.Width = 96
.Height = 55.5
If Cell.Row = 1 Then
.Top = Cell.Top + 5
Else
.Top = Cell.Top - 7.25
End If
If Cell.Column < (LastCol - 3) Then
.Left = Cell.Offset(0, 1).Left + 11.25
Else
.Left = Cell.Offset(0, (LastCol - Cell.Column) - 3).Left
End If
.Placement = xlFreeFloating
End With
Next
Exit Sub
NoComments:
MsgBox "No comments in sheet"
End Sub

--
Jim
|I have 2 problems with comments in Excel 2007.
|
| 1. Sometimes after I come back to a comment and choose edit, the edit
| box is so far away from the actual cell I have to scroll around
| following the line that connects to it - sometimes miles across the
| spreadsheet in the middle of nowhere. Why does the comment box move so
| much? It displays in the right place if you hover the mouse over it.
|
| 2. Sometimes I edit a comment I have put there myself by right
| clicking and choosing Edit Comment. Nothing happens. If I right click
| and choose Show Comment, then hover, it will pop up, but again often
| far away from the cell.
|
| Any ideas why? It makes working with comments very annoying....
|
| Thanks