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