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,

See Debra Dalgleish's page on resizing comments at:

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


---
Regards,
Norman



"John" wrote in message
...
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" wrote in message
...
Hi John,

See Debra Dalgleish's page on resizing comments at:

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


---
Regards,
Norman



"John" wrote in message
...
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


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=466186

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 = 100
.Height = 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.Au toS*ize = True
On Error Goto 0

Hope this is useful
Ken Johnson