Rescaling a Comment text-box

[Andrew Hall NZ]

I am creating many comments as part of a large automated workbook. I would
like to rescale the comment box since the default is far larger than it needs
be. I tried recording a macro for the rescaling and got:

Sub Macro4()
Range("DZ16").Comment.Text Text:="31/05/2007"
Selection.ShapeRange.ScaleHeight 0.5, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleWidth 0.5, msoFalse, msoScaleFromTopLeft
End Sub

This macro does not actually work, failing with run time error 438, "object
does not support this property or method".

Is there any way to achieve this?

Andrew

 

[Gary''s Student]

Adjusting comments is a pain in the a**

1. select a cell
2. make the comment visible
3. select the shape of the comment
4. do your thing:



Sub Macro4()
Range("D7").Select
Range("D7").Comment.Visible = True
Range("D7").Comment.Shape.Select
Selection.ShapeRange.ScaleWidth 10, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 10, msoFalse, msoScaleFromTopLeft
End Sub

 

[Andrew Hall NZ]

Thanks Gary, that works perfectly.

I agree comment handling is a pain. Unfortunately they are also very useful
sometimes.

Andrew

 

[Max]

Another relevant link is Debra's page at:
http://www.contextures.com/xlcomments03.html#Resize
Resize all comments in the selected area

Look for the Sub ResizeCommentsInSelection()
by Dave Peterson