Resize Comments Box

[John]

Can anyone explain preferably with an example how you set the size or
re-size a cells comments box using vba code.

Regards

John

[dominicb]

Good morning John

Sub Macro1()
' This will resize to your specification.
Range("A1").Comment.Shape.Select True
Selection.ShapeRange.ScaleWidth 1.5, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 2.5, msoFalse, msoScaleFromTopLeft
End Sub

Sub macro2()
' This will autosize the comment box
Range("A1").Comment.Shape.TextFrame.AutoSize = True
End Sub

Two example of how to resize a comment box, attached to cell A1.

HTH

DominicB

[Norman Jones]

Hi John,

See Debra Dalgleish's page on resizing comments at:

http://www.contextures.com/xlcomments03.html#Resize


---
Regards,
Norman



"John" <[email protected]> wrote in message
news:[email protected]...
> Can anyone explain preferably with an example how you set the size or
> re-size a cells comments box using vba code.
>
> Regards
>
> John
>

[John]

Thanks Norman

"Norman Jones" <[email protected]> wrote in message
news:Ol%[email protected]...
> Hi John,
>
> See Debra Dalgleish's page on resizing comments at:
>
> http://www.contextures.com/xlcomments03.html#Resize
>
>
> ---
> Regards,
> Norman
>
>
>
> "John" <[email protected]> wrote in message
> news:[email protected]...
>> Can anyone explain preferably with an example how you set the size or
>> re-size a cells comments box using vba code.
>>
>> Regards
>>
>> John
>>
>
>

[Ken Johnson]

Hi John,
If you Want to Make the comment TextFrame a particular height and
width you could use something like the following which resizes the
comment in A1 of the activesheet to a height of 50 points and a width
of 100 points:


On Error Resume Next 'Stop error when no comment
With ActiveSheet.Range("A1").Comment.Shape
.Width =3D 100
.Height =3D 50
End With
On Error GoTo 0

Alternatively, if you just want the comment textframe to automatically
accommodate a different size comment you could just use:

On Error Resume Next 'Stop error when no comment
Activesheet.Range("A1").Comment.Shape.TextFrame.AutoS=ADize =3D True
On Error Goto 0

Hope this is useful
Ken Johnson