macro to draw shapes

[still learning]

Hi
Is there a way to have a macro open the "insert" tab and pick a certain shape to draw?

Mike

 

[BobUmlas]

Sub ShowShapes()
SendKeys ("%h")
SendKeys ("~")
SendKeys "%nSh"
End Sub

 

[Lidsavr]

Still Learning,
You have to know what shape you want to create. Here is one that I use to create a rectangle that acts as button to open a pivot table. First, I create the button, then I move it to the cell where I want it positioned and last assign a macro to it.

Private Sub Create_2312_ICT_TD_toPivot_Button()
   Dim btn As Shape
   Sheets("Insert").Select
   
[COLOR="green"]'  Creates rectangle [/COLOR]   
    Set btn127 = ActiveSheet.Shapes.AddShape _
        (msoShapeRectangle, 10, 10, 95.76, 18)
    btn.Fill.ForeColor.RGB = RGB(0, 51, 102)

[COLOR="green"]'   Moves the button to cell E2 [/COLOR]    
    btn.Select
    Selection.Cut
    Range("E2").Select
    ActiveSheet.Paste

[COLOR="green"]'   Assigns a macro action to the button[/COLOR]    
    With Selection
        .Caption = "View Pivot Table"
        .Font.Color = RGB(255, 255, 255)
        .Font.FontStyle = "Bold"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .OnAction = "Action127"   [COLOR="Green"]'This is another macro I have coded[/COLOR]
    End With
    Range("A1").Select
End Sub

If you want different shapes based on cell conditions, you will have to use IF statements in the code.

Hope this helps.

Charles

 

[Lidsavr]

Still Learning,
There is a problem with the code I posted. I forgot to remove the "127" after btn in line 6 of the code.

The line should read:

    Set btn = ActiveSheet.Shapes.AddShape _
        (msoShapeRectangle, 10, 10, 95.76, 18)

Sorry for the error.

Charles

 

[still learning]

Hi BobUmlas, Lidsavr
Thanks for the quick responses

I tried the Sub showshapes(). It brings up the box like I want, but I don't know how to have it pick the shape I want (the line with the arrow at one end) and change the cursor to the cross so I can draw it where I want.

I tried the Private Sub (etc.) taking out the "127" stuff. I got a message box that said subscript out of range.

I like the idea of using a macro to make a macro button and assigning a macro to it. I'm using 2007 and have made icons on the ribbon that only show in a particular spreadsheet. They work but I get a message box after saying it is an incorrect function This could solve that. I could have a button made and erased after it does it's thing

Mike

 

[Lidsavr]

still learning,
I also use 2007. The action button code that I have opens a pivot table on another worksheet and creates another button to return to the first page. (When I click the second button, it deletes the button before returning to the first page). This sounds like what you want to do.

The only reason why you may get a subscript error is is that you do not a have another macro defined to action upon. Try the code without the action step as shown below:

Private Sub Create_Button()
   Dim btn As Shape
   Sheets("Sheet1").Select  [COLOR="Green"]'Insert your sheet name[/COLOR]
   
'  Creates rectangle    
    Set btn = ActiveSheet.Shapes.AddShape _
        (msoShapeRectangle, 10, 10, 95.76, 18)
    btn.Fill.ForeColor.RGB = RGB(0, 51, 102)

'   Moves the button to cell E2     
    btn.Select
    Selection.Cut
    Range("E2").Select
    ActiveSheet.Paste

'   Assigns a macro action to the button    
    With Selection
        .Caption = "New Shape"
        .Font.Color = RGB(255, 255, 255)
        .Font.FontStyle = "Bold"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
     End With
    Range("A1").Select
End Sub

If you need another macro for an action step, we can work on that, but try the code first. It should work.

If not, please provide the exact error.

Regards,

Charles

 

[still learning]

Lidsavr,
I made a test macro and added it to .OnAction..etc.... I got everything to work A question : How can I create the box in the active cell (where the cursor is) without creating it in e2 and moving it.. Now I can make a macro button, use it and then delete it before I saveand close. sweet

This will make a box. What code would I use to have a macro pick one of the "lines" and let me draw it, then "end sub". Where would I find the name (mso...line with an arrow at one end...).

Mike