[RESOLVED] Clicking a shape via VBA

[MartinLiss]

I have a worksheet that contains several shapes, and macros are assigned to each of them. Let's call one of those shapes shpGS and the macro it triggers GoodStuff. I use Application.Caller in GoodStuff to determine which macro to execute and when I manually click shpGS, GoodStuff is executed and it works great. However I'd like some way of clicking the shape programmatically. Calling GoodStuff doesn't work because Application.Caller does not get created unless the shape itself is clicked and so GoodStuff fails with an error.

Is there any way to "click" shpGS programmatically?

[OptionBase1]

I have a worksheet that contains several shapes, and macros are assigned to each of them. Let's call one of those shapes shpGS and the macro it triggers GoodStuff. I use Application.Caller in GoodStuff to determine which macro to execute and when I manually click shpGS, GoodStuff is executed and it works great. However I'd like some way of clicking the shape programmatically. Calling GoodStuff doesn't work because Application.Caller does not get created unless the shape itself is clicked and so GoodStuff fails with an error.

Is there any way to "click" shpGS programmatically?

Seems like a bit of a Rube-Goldberg. Simulate a click so that an object is populated and a method is called and the value of the object is checked so that the appropriate macro is executed?

Why not just directly execute the appropriate macro?

[MartinLiss]

Why not just directly execute the appropriate macro?

Because as I said, the macro uses Application.Caller to determine which shape is calling the macro and unless you manually click the shape (or hoping somehow simulate the manual click through VBA), the Application.Caller line errors out.

[OptionBase1]

Without seeing code, I stand by my suggestion. There's no reason to try to simulate a click of anything if you know in the end what code it is you want to execute. It may require moving some specific code into it's own routine if you have a bunch of code in a Click handler directly.

[MartinLiss]

Here is a demo workbook that illustrates the problem.

[Dan_W]

If doing it programmatically, you could pass the shape name (or whatever) to the subroutine as a parameter. By making the parameter optional in the subroutine, you can still call it from clicking on shapes.

Option Explicit

Sub AppCaller(Optional Param As Variant)

    On Error GoTo ErrHandler

    Dim ShapeSelection As String
    If IsMissing(Param) Then
        ShapeSelection = Application.Caller
    Else
        ShapeSelection = CStr(Param)
    End If
    
    Select Case ShapeSelection
        Case "shp1"
            DoSomething1
        Case "shp2"
            DoSomething2
        Case Else
            MsgBox ShapeSelection
    End Select
    
ErrHandler:
    If Err.Number <> 0 Then MsgBox Err.Number & vbNewLine & Err.Description
    
End Sub

Sub DoSomething1()
    MsgBox "Do something one way"
End Sub

Sub DoSomething2()
    MsgBox "Do something another way"
End Sub

Sub Problem()
    AppCaller
End Sub

Sub OnePossibility()
    AppCaller "Pass the name of the shape as a parameter?"
End Sub

[MartinLiss]

That works. Thank you.