Changing the size of Comment Shapes

I need to change the size on a great many comments on a Worksheet. I find
that this code:

Selection.ShapeRange.ScaleHeight 1.6, msoFalse, msoScaleFromTopLeft

will change, but relative to the default size. I can't find the syntax to
change to Height and Width settings.

Also, how can I change the default size?

Thanks for any help.

Sub ac()
Range("B9").Comment.Shape.Height = 40
Range("B9").Comment.Shape.Width = 80
End Sub


--
Regards,
Tom Ogilvy




"Randy Harris" <[email protected]> wrote in message
news:[email protected]...
> I need to change the size on a great many comments on a Worksheet. I find
> that this code:
>
> Selection.ShapeRange.ScaleHeight 1.6, msoFalse, msoScaleFromTopLeft
>
> will change, but relative to the default size. I can't find the syntax to
> change to Height and Width settings.
>
> Also, how can I change the default size?
>
> Thanks for any help.
>

Randy,

Try something like

Dim CMT As Comment
Set CMT = ActiveSheet.Comments(1)
CMT.Shape.Height = 100
CMT.Shape.Width = 200

I don't believe you can change the default size.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Randy Harris" <[email protected]> wrote in message
news:[email protected]...
>I need to change the size on a great many comments on a
>Worksheet. I find
> that this code:
>
> Selection.ShapeRange.ScaleHeight 1.6, msoFalse,
> msoScaleFromTopLeft
>
> will change, but relative to the default size. I can't find
> the syntax to
> change to Height and Width settings.
>
> Also, how can I change the default size?
>
> Thanks for any help.
>

"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> Sub ac()
> Range("B9").Comment.Shape.Height = 40
> Range("B9").Comment.Shape.Width = 80
> End Sub
>
>
> --
> Regards,
> Tom Ogilvy

Thanks. You make it seem so simple. I couldn't figure it out. I must have
some sort of mental block or something.

Again, thanks,
Randy Harris

Hi Randy,
Another option is a close fitting comment.

Resizing All Cell Comments (#resizing)
http://www.mvps.org/dmcritchie/excel...t.htm#resizing

The following may be a fixup if you have serious problems for size of the comment box. (Format, Alignment, Automatic size). .

Sub FitComments()
Dim c As Comment
For Each c In ActiveSheet.Comments
c.Shape.TextFrame.AutoSize = True
Next c
End Sub
---
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

"Randy Harris" <[email protected]> wrote in message ..
> I need to change the size on a great many comments on a Worksheet.