Comment box sizing

[Paul Hunter]

My thanks to William West for his assistance in providing
the following code.

What I need to do is to set a standard size for the
comment boxes. So far, the longest comment is 689
characters in length. I had found some code that auto-
sized the comment box but this just put the entire comment
on a single line. So, my question is: How do I set a
standard size for all comment boxes?
Comment box = X width by X height

Here's the code:

Sub ConvertTOComments()
With ActiveSheet
Dim r As Range, c As Range
Set r = .Range("cmts")
For Each c In r
c.AddComment
c.Comment.Text Text:=c.Text
Next c
End With
End Sub

Thanks for you assistance.

 

[David McRitchie]

Hi Paul,
Resizing should fit what you have, but here is what you asked for

Cell Comments
http://www.mvps.org/dmcritchie/excel/ccomment.htm#resizing

 

[Paul Hunter]

Thanks David. Here's what I ended up with:

Sub ConvertTOComments()
With ActiveSheet
Dim r As Range, c As Range
Set r = .Range("cmts")
For Each c In r
c.AddComment
c.Comment.Text Text:=c.Text
c.Comment.Shape.Width = X
c.Comment.Shape.Height = X
Next c
End With
End Sub

Now I just have to experiement to find just the right
sizes for X.