autofit shape

[lior03]

hello
the followingt macro insert a shape in activesheet.
it add a text.how can i autofit the shape?
[VBA]
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 510#, 1347.75, 126.75, 102.75) _
.Select
selection.Characters.Text = "??""? ???? ?? ???." & Chr(10) & "????? ??? ???? , ???."
With selection.Characters(Start:=1, Length:=38).Font
.Name = "Arial (Hebrew)"
.FontStyle = "bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlNone
.ColorIndex = 5
End With
End Sub

[/VBA]
thanks

[Andy Pope]

Hi,

For a change to your code use

ActiveSheet.Shapes.AddShape(msoShapeRectangle, 510#, 1347.75, 126.75, 102.75) _
.Select
Selection.Characters.Text = "??""? ???? ?? ???." & Chr(10) & "????? ??? ???? , ???."
With Selection.Characters(Start:=1, Length:=38).Font
    .Name = "Arial (Hebrew)"
    .FontStyle = "bold"
    .Size = 12
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlNone
    .ColorIndex = 5
    .Parent.Parent.AutoSize = True
End With

Although revised code make it a little clearer what exactly is being done

With ActiveSheet.Shapes.AddShape(msoShapeRectangle, 510#, 1347.75, 126.75, 102.75)
    .TextFrame.Characters.Text = "??""? ???? ?? ???." & Chr(10) & "????? ??? ???? , ???."
    With .TextFrame.Characters(Start:=1, Length:=38).Font
        .Name = "Arial (Hebrew)"
        .FontStyle = "bold"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlNone
        .ColorIndex = 5
    End With
    .TextFrame.AutoSize = True
End With