I have a large worksheet with approx. 5,000 comments. Can I change the
default size, font, etc to make all of them easier to read in one fell swoop?
Thanks much.

Code from Debra Dalgleish to re-format all Comments on all sheets in a workbook

Sub FormatAllComments()
Dim ws As Worksheet
Dim cmt As Comment
For Each ws In ActiveWorkbook.Worksheets
For Each cmt In ws.Comments
With cmt.Shape.TextFrame.Characters.Font
.Name = "Times New Roman"
.Size = 12
End With
Next cmt
Next ws
End Sub


Gord Dibben MS Excel MVP


On Tue, 31 Jul 2007 10:40:06 -0700, AJB wrote:

I have a large worksheet with approx. 5,000 comments. Can I change the
default size, font, etc to make all of them easier to read in one fell swoop?
Thanks much.

How can I format the size of the comment box to make all of them easier to
read?



"Gord Dibben" wrote:

Code from Debra Dalgleish to re-format all Comments on all sheets in a workbook

Sub FormatAllComments()
Dim ws As Worksheet
Dim cmt As Comment
For Each ws In ActiveWorkbook.Worksheets
For Each cmt In ws.Comments
With cmt.Shape.TextFrame.Characters.Font
.Name = "Times New Roman"
.Size = 12
End With
Next cmt
Next ws
End Sub


Gord Dibben MS Excel MVP


On Tue, 31 Jul 2007 10:40:06 -0700, AJB wrote:

I have a large worksheet with approx. 5,000 comments. Can I change the
default size, font, etc to make all of them easier to read in one fell swoop?
Thanks much.

See Debra's site

http://www.contextures.on.ca/xlcomments03.html#Resize

For more on Excel tips and tricks................Her Excel homepage is

http://www.contextures.on.ca/tiptech.html


Gord Dibben MS Excel MVP

On Tue, 31 Jul 2007 12:50:01 -0700, AJB wrote:

How can I format the size of the comment box to make all of them easier to
read?



"Gord Dibben" wrote:

Code from Debra Dalgleish to re-format all Comments on all sheets in a workbook

Sub FormatAllComments()
Dim ws As Worksheet
Dim cmt As Comment
For Each ws In ActiveWorkbook.Worksheets
For Each cmt In ws.Comments
With cmt.Shape.TextFrame.Characters.Font
.Name = "Times New Roman"
.Size = 12
End With
Next cmt
Next ws
End Sub


Gord Dibben MS Excel MVP


On Tue, 31 Jul 2007 10:40:06 -0700, AJB wrote:

I have a large worksheet with approx. 5,000 comments. Can I change the
default size, font, etc to make all of them easier to read in one fell swoop?
Thanks much.

No.

Because each comment has different shape, content and attribute, with so
many comment in a worksheet, it would be difficult to resize them. An then
comment do not display scroll bar to show that there are more information in
a shape.

This must be possible by writing some code in VBA.

Challa Prabhu

"AJB" wrote:

I have a large worksheet with approx. 5,000 comments. Can I change the
default size, font, etc to make all of them easier to read in one fell swoop?
Thanks much.