Obtain reference of clicked object/image-CodePudding

I've been trying to reproduce a checkbox on Excel with 2 images (1 for Tick and 1 for Untick), but I got a problem because I simply never coded in VBA; apart from macros.

Here's the problem.

I got multiple checkboxes that are meant to be ticked/unticked. I could record a macro for each of them, but doing so would be tedious and unefficient (there's 22 checkboxes in total) The Macro Code looks like this.

Sub Tick()
    ActiveSheet.Shapes.Range(Array("Picture 23")).Select
    Selection.ShapeRange.ZOrder msoSendToBack
End Sub

Sub Untick()
    ActiveSheet.Shapes.Range(Array("Picture 22")).Select
    Selection.ShapeRange.ZOrder msoSendToBack
End Sub

My question is easy : How do I fetch the reference of the image I clicked in order to inject this same reference to the code and set its ZOrder ?

I can't use Excel's Checkboxes because they're too small.

CodePudding user response：

Try following

Sub TickToggle()
On Error GoTo EH
    Dim caller As Variant

    caller = Application.caller
    If Not IsError(caller) Then
        ActiveSheet.Shapes(caller).ZOrder msoSendToBack
        Select Case caller
            Case "Checkbox1_tick":
                ' your code for when the checkbox1 is unticked
            Case "Checkbox1_untick":
                ' your code for when the checkbox1 is ticked
        End Select
    End If
EH:
End Sub

Also, try using more meaningful names for those shapes (like above) in order to use select case on the caller value and do different things depending on what has been clicked. You can assign TickToggle to all checkbox shapes, this is preferred (re-usability).