Comment box resizing

[Queryman]

I have this code that I used to widen the width of comment boxes that appear as po-ups when the mouse is on them, but I get an error when I run the macro in excel 2002. It says the object does not support this property or function.



Sub COMMENT_TEST()
Range("B4&quot.Comment.Text Text:="Michael D:" & Chr(10) & "THIS IS THE TEST COMMENT" & Chr(10) & ""
Selection.ShapeRange.ScaleWidth 1.38, msoFalse, msoScaleFromTopLeft
Range("B4&quot.Select
End Sub



The "Selection" line is where I get the error.

Michael

 

[SkipVought]

Use your Object Browser in the VBE to see what properties and methods are available Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Here is some code that works. There may be an easier way, but I wasn't able to find one.

Option Explicit
Sub Test()
Const WORK_CELL = "B4"
Dim r As Range
Dim i As Integer
  With ActiveSheet
    Set r = Range(WORK_CELL)

    ' Set/reset a comment for the cell...

    On Error Resume Next

 ' In case cell already has comment

    r.AddComment Text:="x"
    r.Comment.Text Text:="Michael D:" & Chr(10) & "THIS IS THE TEST COMMENT" & Chr(10) & ""

    ' Find the comment in the shape in collection and apply a ScaleWidth...

    For i = 1 To .Shapes.Count
      If .Shapes(i).Name = r.Comment.Shape.Name Then
        .Shapes(i).ScaleWidth 1.38, msoFalse, msoScaleFromTopLeft
      End If
    Next i
    Set r = Nothing
  End With
End Sub

Hope this helps