Thanks Jim! You have saved me a lot of grief trying to figure that out.

Cheers,

Jon

"Jim Rech" wrote:

You might try this.

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
"Jon" wrote in message
...
| Hey everyone. I just signed up to have this one issue solved. I have a
large
| spreadsheet that includes many comments. Occasionally the comments move to
| several cells (up to hundreds of cells) below the cell they should be
close
| to or resize so they are not visible. I have researched how to reformat
the
| comment boxes using this macro (see below) that I found on the internet
and
| it works fine except when I go to edit the comments they continue to be
| located in random places and the incorrect size. Any help is greatly
| appreciated.
|
| Option Explicit
|
| Dim cCell As Range
|
| Dim sComment As Comment
|
| Dim allComments As Range
|
|
|
| Sub reset_comments()
|
| On Error Resume Next
|
| Application.ScreenUpdating = False
|
| Set allComments = Range("A1").SpecialCells(xlCellTypeComments)
|
| If allComments Is Nothing Then MsgBox "No comments in " &
ActiveSheet.Name:
| GoTo Ex
|
| For Each cCell In allComments
|
| With cCell.Comment
|
| .Shape.Height = 200
|
| .Shape.Width = 125
|
| End With
|
| 'cCell.Select not necessary ?
|
| cCell.Comment.Visible = True
|
| cCell.Comment.Shape.Select True
|
| With Selection
|
| ' .Interior.ColorIndex = 19
|
| .Font.Bold = False
|
| .Font.Size = 10
|
| End With
|
| cCell.Comment.Visible = False
|
| Next cCell
|
| Ex:
|
| Set allComments = Nothing
|
| Application.ScreenUpdating = True
|
| End Sub
|
|