Creating a rectangle and assigning a macro

[dannybland]

I need to create a few boxes in my sheet (excel 2007) and then assign a macro to them, however, the macro will not be in the workbook as I am building a macro to my personal that I will use to format different reports from different sheets that are run daily.

I have the below code which is ideal for the shape and placement, but I need help with getting the macro assigned to it. (It can be any generic macro right now as I will be developing the macro later and will likely just make a pivot table according to which button is clicked.)

With ActiveSheet.Shapes.AddShape(msoShapeRectangle, Range("B2").Left, Range("B2").Top, 200, 100).TextFrame
.Characters.Text = "This is a rectangle"
.HorizontalAlignment = xlHAlignCenter
.VerticalAlignment = xlVAlignCenter
End With

Once this is done I will look to add a rectangle that asks the user for the criteria for the pivot table but that seemed like a step ahead of where I am at, but if you can help with this also, many thanks!

 

[dmt32]

Hi,
see if this change to your code helps:

Sub addshape()
    Dim obj As Shape


    Set obj = ActiveSheet.Shapes.AddShape(msoShapeRectangle, Range("B2").Left, Range("B2").Top, 200, 100)


    With obj
        With .TextFrame
            .Characters.Text = "This is a rectangle"
            .HorizontalAlignment = xlHAlignCenter
            .VerticalAlignment = xlVAlignCenter
        End With
        .OnAction = "test"
    End With
End Sub




Sub test()
    MsgBox "Hello"
End Sub

Dave

 

[dannybland]

Hi,

Thanks for the help, the test macro doesn't work when the box is clicked on as the macro isn't in the workbook, its in my personal. Can it be accessed from there or does it need to be in the workbook?

If it needs to be in the workbook, can you assist with how to do that in the macro?

Thanks,
Danny

 

[dannybland]

I got it! Just needed to add the PERSONAL.XLSB! to the front!

Sub addshape()
Dim obj As Shape




Set obj = ActiveSheet.Shapes.addshape(msoShapeRectangle, Range("B2").Left, Range("B2").Top, 200, 100)




With obj
With .TextFrame
.Characters.Text = "This is a rectangle"
.HorizontalAlignment = xlHAlignCenter
.VerticalAlignment = xlVAlignCenter
End With
.OnAction = "PERSONAL.XLSB!test"
End With
End Sub
Sub test()
MsgBox "Hello"
End Sub