Comments

[Jon]

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

 

[Jim Rech]

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
| 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
|
|

 

[Jon]

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

Cheers,

Jon