Run a macro from inside a macro - based on the selected shape name - selected by the current macro

[Jamie McMillan]

Hello,

I have Shapes named ,1, 2, 3, 4
The Macros assigned to each shape are called, 1, 2, 3, 4

Another shape called A, 'macro called A', selects one of these shapes, based on a cell value, Range("Q2").Value, of (1, 2, 3 or 4)

If the macro selects shape 1 I want it to then Call 1 (Call Macro 1), If the macro selects shape 3 I want it to then Call 3 (Call Macro 3)

Sub onetwo()

Dim Mac As Variant (I have tried using, long, string, ... etc)
Set Mac = ActiveSheet.Shapes(Application.Caller).Name (I have tried not using Set - just Mac = ActiveSheet.Shapes(Application.Caller).Name )

If Range("Q2").Value = 1 Then
ActiveSheet.Shapes.Range(Array("1")).Select
Selection.ShapeRange.ZOrder msoBringToFront
Else
If Range("Q2").Value = 2 Then
ActiveSheet.Shapes.Range(Array("2")).Select
Selection.ShapeRange.ZOrder msoBringToFront
Else
If Range("Q2").Value = 3 Then
ActiveSheet.Shapes.Range(Array("3")).Select
Selection.ShapeRange.ZOrder msoBringToFront
Else
If Range("Q2").Value = 4 Then
ActiveSheet.Shapes.Range(Array("4")).Select
Selection.ShapeRange.ZOrder msoBringToFront
End If
End If
End If
End If

Call Mac
End Sub

 

[GWteB]

Use of the Application.Caller property would not be useful in this case because it would always return the same value, i.e. the name of the button or shape which was pressed to run this macro.

The code below does exactly as what you're trying to do. Note that, as in your code, there is no check anywhere whether the shape in question exists and/or whether a macro has been assigned to that shape. Hope this helps.

Sub JamieMcMillan()
    
    Dim ShpName As String, Shp  As Shape

    ShpName = Range("Q2").Value
    Select Case ShpName
        Case 1, 2, 3, 4
        Set Shp = ActiveSheet.Shapes(ShpName)
        With Shp
            .ZOrder msoBringToFront
            .Select
            Application.Run .OnAction
        End With
    End Select
End Sub