Please Help: VBA -OnAction returns "Cannot Run...

[Lidsavr]

I am using Excel 2007 and creating code that will run in Excel 2003.

Can someone help me with my code? It is not executing the OnAction macro.

This is my first time creating a shape that executes a macro and I am getting a popup message when I click on the shape that says, "Cannot run the macro Shapeclick1. The macro may not be available in this workbook or all macros may be disabled"

1. The macro is available in the same module as the code that created the shape.
2. Macros are not disabled because I created the shape successfully (minus the OnAction command working)

Here are the two subroutines that create the shape and then the macro it is supposed to run:

Public newbtn As Shape

Private Sub Create_232_ICT_TD_toPivot_Button()
    Sheets("232 Charts").Select
    Set newbtn = ActiveSheet.Shapes.AddShape _
        (msoShapeRectangle, 10, 10, 95.76, 18)
    newbtn.Fill.ForeColor.RGB = RGB(6, 56, 109)
    newbtn.Select          '  Selects the shape
    Selection.Cut          '  Cuts the shape
    Range("E2").Select
    ActiveSheet.Paste          'positions the shape in cell E2
    With Selection
        .Caption = "View Pivot Table"
        .Font.Color = RGB(255, 255, 255)
        .Font.FontStyle = "Bold"
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .OnAction = "ShapeClick1"
    End With
End Sub

The code above successfully creates the shape, but the .OnAction is not working.

Private Sub ShapeClick1()
    Sheets("Top Defects Pivot").Select          
    ActiveSheet.PivotTables("Top_Defects").PivotFields _
        ("Assembly(s)").CurrentPage = "ARCT00232"    'Changes the PT Assembly #
    ActiveSheet.PivotTables("Top_Defects") _
        .PivotFields("Proc").CurrentPage = "IF1"     ' Changes the PT dept.

A note on the private subs: I like the subroutines to be private so users cannot see them on the macro list. Public variables work in other code I have written. I tried removing the private from the Shapeclick1 sub and still receive the same error after recreating the shape.

Thank you in advance for your help.
Charles

 

[Hermanito]

Is the Create_232_ICT_TD_toPivot_Button() sub in a real code module, or in a sheet code module?

I think that .OnAction can only call subs in real code modules, although I am not entirely sure of that. So if it is in a sheet code module, you could try putting it in a real code module. You'd have to change it to public to work then though.

Another way to keep public subs from showing up in the Run Macro window is either give it a dummy input variable you don't use (give it a default value as well, so you don't even have to provide it when calling the sub); or just change the sub into a function and return a dummy value, for instance a boolean.