How to set the comment size in VBA

[ssunchen]

Hi,

I used Record Macro to create a comment. The VBA code is as follow:

Range("C1").Select
Range("C1").AddComment
Range("C1").Comment.Visible = False
Range("C1").Comment.text text:="Test: A1 " & Chr(10) & "Result: 56"
Selection.ShapeRange.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft


Once run Macro, get a error message:"Object doesn't support this property or method" for the statement "Selection.ShapeRange.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft"


Please help!! Big Thanks!!
ssunchen

[smozgur]

Try this one: you are on the right way, i just used related Comment.Shape object to set scaleheight and width.

With Range("C1")
.Select
.AddComment
With .Comment
.Visible = False
.Text Text:="Test: A1 " & Chr(10) & "Result: 56"
.Shape.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft
.Shape.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
End With
End With


Regards

[ This Message was edited by: smozgur on 2002-04-20 14:54 ]

[ This Message was edited by: smozgur on 2002-04-20 14:54 ]

[Ivan F Moala]

On 2002-04-20 12:36, ssunchen wrote:
Hi,

I used Record Macro to create a comment. The VBA code is as follow:

Range("C1").Select
Range("C1").AddComment
Range("C1").Comment.Visible = False
Range("C1").Comment.text text:="Test: A1 " & Chr(10) & "Result: 56"
Selection.ShapeRange.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft


Once run Macro, get a error message:"Object doesn't support this property or method" for the statement "Selection.ShapeRange.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft"


Please help!! Big Thanks!!
ssunchen

You haven't got the comment selected...but
then there is no need to select an object
in order to change it.

Try:

Range("C1").Select

Range("C1").AddComment

Range("C1").Comment.Visible = False

Range("C1").Comment.Text Text:="Test: A1 " & Chr(10) & "Result: 56"

Range("C1").Comment.Shape.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft

Range("C1").Comment.Shape.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft

Or tidier;


Sub Tidy()
With Range("C1")
.AddComment
.Comment.Text Text:="Test: A1 " & Chr(10) & "Result: 56"
.Comment.Shape.ScaleWidth 5.87, msoFalse, msoScaleFromTopLeft
.Comment.Shape.ScaleHeight 2.26, msoFalse, msoScaleFromTopLeft
End With
End Sub

[Jack in the UK]

Comments eh! i hated them as BOARING in till Ivan F Moala showed me they ant gotta be i have sweet little blue and white clouds!

What i found was the size was a pain and recorded them if regula size was required and so saved the agro and added the lines of code in, thus me being lazy.

i sent Ivan these as thanks to him for the pointer, sad no excel i have so no code at disposal.. Thus why i rairly post my VBA