How to Get the Clicked Shape Name in an Excel VBA Macro
Question details
The user needs to retrieve the name of the specific shape that was clicked when multiple Excel shapes are assigned to trigger the exact same VBA OnAction macro.
- Product
- Excel VBA
- Device & OS
- not provided
- Scenario
- Running a single VBA macro assigned to multiple shapes on a worksheet and dynamically identifying which shape was clicked by the user to trigger the code.
- Observed behavior
- The macro accurately identifies the clicked shape by assigning the shape's name to a string variable using the Application.Caller property.
Ensure that the Developer tab is enabled in your spreadsheet and that your worksheet contains multiple shapes, all assigned to the same target macro.
Use Application.Caller in Your VBA Macro
Retrieve the name of the calling shape dynamically by utilizing the Application.Caller property within the assigned OnAction macro.
When multiple shapes on an Excel worksheet are assigned to the exact same macro, VBA handles the event seamlessly. You do not need to write separate macros for every single shape. Instead, the Application.Caller property automatically captures and returns the name of the specific shape that initiated the script.
This returned value can then be stored as a String variable and used within your VBA logic to manipulate the specific clicked shape or run conditional statements.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the Project Explorer, double-click the Module containing your macro, or click Insert > Module to create a new one.
Inside your macro sub-procedure, declare a string variable and assign Application.Caller to it by typing: Dim shName As String followed by shName = Application.Caller.
If you are debugging or need to verify the data type being returned by the caller, you can use the code VBA.TypeName(Application.Caller) to confirm it is handled correctly.
Easily Manage Macros and Shapes with WPS Spreadsheet
WPS Office offers robust support for VBA macros, allowing you to run, edit, and assign dynamic scripts to shapes just like you do in Microsoft Excel. Enhance your productivity with a fully compatible, lightweight spreadsheet environment.
- 1. Enable the Developer Tab: Open WPS Spreadsheet, go to the Menu, click Options, and check 'Developer' in the Customize Ribbon settings.
- 2. Insert Your Shapes: Navigate to the Insert tab, select Shapes, and draw the required interactive objects directly onto your worksheet.
- 3. Write the VBA Code: Click 'Visual Basic' in the Developer tab to open the VBA editor and paste your Application.Caller macro.
- 4. Assign the Macro: Right-click the inserted shapes, click 'Assign Macro', and select your macro from the list to make them interactive.
Frequently Asked Questions
Why does Application.Caller return an Error 2023?
If you run the macro directly from the VBA editor (by pressing F5) rather than clicking a shape on the worksheet, Application.Caller cannot identify a caller and will return an error. It only works when the macro is triggered by an object click.
Can I use Application.Caller to get the clicked shape's text instead of its name?
Application.Caller strictly returns the shape's name. However, you can use that name to reference the shape object and extract its text using code like: ActiveSheet.Shapes(Application.Caller).TextFrame2.TextRange.Text.
Does Application.Caller work in WPS Spreadsheet macros?
Yes, WPS Spreadsheet supports VBA macros in compatible editions and correctly processes the Application.Caller property when an assigned shape is clicked on the worksheet.
How do I quickly assign the same macro to multiple shapes?
You can assign a macro in bulk by holding the Ctrl key, clicking to select all desired shapes, right-clicking one of the selected shapes, choosing 'Assign Macro...', and selecting your target macro.
