Prev: "Do you want to update" msg
Next: Conditional Formatting v VBE code for desired effect
From: Henk on 27 May 2010 17:24
How do I resize a comment box in VB?

When I record a macro I get :

Range("C9").Select
Range("C9").AddComment
Range("C9").Comment.Visible = False
Range("C9").Comment.Text Text:="Bladiblabla"
Selection.ShapeRange.ScaleHeight 0.3, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleWidth 0.1, msoFalse, msoScaleFromTopLeft

Running this code results in :

Runtime error '438':
Object doesn't support this property or method

Anyone any idea?

Thanks in advance.
From: Jacob Skaria on 27 May 2010 17:28
Try

Sub AddComment()

Dim ComBox As Comment
On Error Resume Next
Set ComBox = Range("C9").AddComment("Bladiblabla")
On Error GoTo 0

If Not ComBox Is Nothing Then
With ComBox
..Visible = False
..Shape.ScaleHeight 0.3, msoFalse, msoScaleFromTopLeft
..Shape.ScaleWidth 0.1, msoFalse, msoScaleFromTopLeft
End With
Set ComBox = Nothing
End If

End Sub


--
Jacob (MVP - Excel)


"Henk" wrote:

> How do I resize a comment box in VB?
>
> When I record a macro I get :
>
> Range("C9").Select
> Range("C9").AddComment
> Range("C9").Comment.Visible = False
> Range("C9").Comment.Text Text:="Bladiblabla"
> Selection.ShapeRange.ScaleHeight 0.3, msoFalse, msoScaleFromTopLeft
> Selection.ShapeRange.ScaleWidth 0.1, msoFalse, msoScaleFromTopLeft
>
> Running this code results in :
>
> Runtime error '438':
> Object doesn't support this property or method
>
> Anyone any idea?
>
> Thanks in advance.
 | 
Pages: 1
Prev: "Do you want to update" msg
Next: Conditional Formatting v VBE code for desired effect