Drawing and Placing Shapes

[dgrimm]

I am creating a report that I am creating buttons (shapes) and attaching macros to the shapes to move around the report.

This is what I am needing help on.

I want to create a rounded rectangle and lets say size = 1" by 3" with the text "Goto Help Screen" placed in the upper left of cell "K7" with a red color.

Thank you

Dave

 

[Trevor G]

Record a macro and the draw the shape this will then give you the code you need.

 

[dgrimm]

I have tried to record the drawing of the shape but all the recording gives me is the cell that I select.

Thanks though.

Dave

 

[Trevor G]

You can get some help for the VBA Help system, I didn't realise that when recording shapes it doesn't record the code. Sorry Dave I seemed to miss lead you here.

Here is a basic code that it comes up with. It will get you going.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Macro3()<br><SPAN style="color:#00007F">Set</SPAN> myDocument = Worksheets(1)<br>myDocument.Shapes.AddShape msoShapeRectangle, 50, 50, 100, 200<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Trevor G]

Just created this.

Sub ashape()
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 72, 72, 72, 72).Select
With Selection
.Characters.Text = "Help File"
.ShapeRange.Fill.Visible = msoTrue
.ShapeRange.Fill.ForeColor.SchemeColor = 2
.ShapeRange.Line.Visible = msoTrue
.ShapeRange.Line.ForeColor.SchemeColor = 64
.ShapeRange.Line.BackColor.RGB = RGB(0, 255, 0)
.Name = "Prepared_by_box"
'.OnAction = "eng_design_box"
.Top = Range("k5").Top
.Left = Range("k5").Left
End With
End Sub

 

[dgrimm]

Trevor thank you for all the help thus far....I am needing one more thing.

Could you help on making it a beveled rounded rectangle.

Thank you

 

[Trevor G]

Im not totally convinced you can do this, but this will give you a bevel shape.

Sub ashape()
ActiveSheet.Shapes.AddShape(msoShapeBevel, 72, 72, 72, 72).Select
With Selection
.Characters.Text = "Help File"
.ShapeRange.Fill.Visible = msoTrue
.ShapeRange.Fill.ForeColor.SchemeColor = 2
.ShapeRange.Line.Visible = msoTrue
.ShapeRange.Line.ForeColor.SchemeColor = 64
.ShapeRange.Line.BackColor.RGB = RGB(0, 255, 0)
.Name = "Prepared_by_box"
.OnAction = "eng_design_box"
.Top = Range("k5").Top
.Left = Range("k5").Left
End With
End Sub

Also here is a link that will list the msoShapes you can use.
http://msdn.microsoft.com/en-us/library/aa432469(v=office.12).aspx