Create and place Auto Shape with VBA

[depcdivr]

I am working on a VBA program that I want to have add some autoshapes to a worksheet. However I am having a hard time figuring out how to control the location of the autoshape. I tried recording a macro but it is still not making any sense to me. Can somebody help explain how to control the size and location of a autoshape with VBA?

Here is the Code that I am starting with.

    ActiveSheet.Shapes.AddShape(msoShapeRectangle, 228#, 557.25, 126#, 13.5).Select
    With Selection
        .Characters.Text = "Click Here"
        .ShapeRange.Fill.Visible = msoTrue
        .ShapeRange.Fill.ForeColor.SchemeColor = 43
        .ShapeRange.Line.Visible = msoTrue
        .ShapeRange.Line.ForeColor.SchemeColor = 64
        .ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
        .Name = "Prepared_by_box"
        .OnAction = "eng_design_box"
     End With

 

[VoG]

Is this what you mean?

Sub ashape()
    ActiveSheet.Shapes.AddShape(msoShapeRectangle, 228#, 557.25, 126#, 13.5).Select
    With Selection
        .Characters.Text = "Click Here"
        .ShapeRange.Fill.Visible = msoTrue
        .ShapeRange.Fill.ForeColor.SchemeColor = 43
        .ShapeRange.Line.Visible = msoTrue
        .ShapeRange.Line.ForeColor.SchemeColor = 64
        .ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
        .Name = "Prepared_by_box"
        .OnAction = "eng_design_box"
        .Top = Range("C3").Top
        .Left = Range("C3").Left
     End With
End Sub

 

[depcdivr]

That worked nicely. Can I control the width by using something like

.Top = Range("C3").Top
.Left = Range("C3").Left
.Bottom = Range("D3").Bottom
.Right = Range("D3").Right