CODEWITHSUNDEEP
excelvba
jafri
jafri

Reputation: 19

Is there a way for excel to recognize which shape having macro assigned was clicked?

I have added a few shapes to my worksheet. I have assigned macros to them. But I had to create separate macros for all.

Is there a way for excel to recognize which shape was clicked and based on its text value find the cell to copy. That way I need not have to create separate macros for each shape.

I have tried searching for it online, found a few things which I tried but did not work.

Any help would be greatly appreciated.

Upvotes: 1

Views: 502

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57743

Yes, the Application.Caller property can be used here.

Option Explicit 

Public Sub MyShapeMacro_Click()
    Dim CalledByShape As Shape
    Set CalledByShape = ActiveSheet.Shapes(Application.Caller)

    'CalledByShape is your shape
    Debug.Print CalledByShape.OLEFormat.Object.Text 'returns the text of the shape
End Sub

Link this macro MyShapeMacro_Click to all your shapes.

Note this macro cannot be started in the VBA editor (it will throw an exception) it can only be run by clicking the shape.

Upvotes: 1

Related Questions