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