Can anyone explain preferably with an example how you set the size or
re-size a cells comments box using vba code.
Regards
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
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
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
>
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
>>
>
>
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks