Comment box sizing

P

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

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.
-----Original Message-----
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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Paul Hunter said:
How do I set a
standard size for all comment boxes?
Comment box = X width by X height
Click to expand...



.
Click to expand...
 
Top