How to Use Application.Caller for Excel Buttons on Multiple Worksheets in WPS Spreadsheets
Question details
The user needs a VBA macro to identify the specific button shape that launched it when the same button is copied across multiple worksheets, instead of depending on a fixed shape name or index.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has copied macro buttons to multiple worksheets and wants one macro to update the clicked button's formatting or behavior on any sheet.
- Observed behavior
- Using a fixed shape name or index fails because copied buttons receive different names or positions, so the macro cannot reliably find the selected button.
Open the workbook in WPS Spreadsheets and make sure the Developer tab is visible: right-click the ribbon, choose Customize Ribbon, and enable Developer if needed. Save a macro-enabled copy as .xlsm so the VBA changes are retained.
Reference the Clicked Button with Application.Caller
Use this method when one macro must work with identical buttons copied to different worksheets without changing the code for each sheet.
Application.Caller returns the name of the shape that started the macro. Assign that name to a Shape variable through ActiveSheet.Shapes so you can alter the exact button the user clicked, rather than a hard-coded shape name or index.
In WPS Spreadsheets, click Developer > Visual Basic or press Alt+F11, then open the module that contains the macro.
Delete fixed names such as Shapes("Button 1") or shape index references from the macro.
Declare a Shape variable and set it to the calling shape with Dim btn As Shape and Set btn = ActiveSheet.Shapes(Application.Caller).
Use btn.Fill.ForeColor.RGB = RGB(0, 176, 80), or another Fill property, to change the clicked button's color.
Save the macro-enabled workbook, click copies of the button on different worksheets, and confirm only the clicked button changes.
Use a Worksheet-Specific Shape Name Lookup
Use this fallback only when Application.Caller is not suitable and you can maintain a table of button names for each worksheet.
Use Application.Caller in WPS Spreadsheets to Manage Copied Macro Buttons
WPS Spreadsheets supports VBA macros, so you can use Application.Caller to identify the exact button shape that triggered the macro and update it across multiple worksheets without fixed names or indexes.
- 1. Open the VBA editor: In WPS Spreadsheets, click the Developer tab and select Visual Basic, or press Alt+F11.
- 2. Open the macro module: In the Project Explorer, find your workbook and open the module where the macro is stored.
- 3. Add Application.Caller code: Create a Shape variable and set it to ActiveSheet.Shapes(Application.Caller).
- 4. Modify the button fill: Change the shape's Fill properties, such as btn.Fill.ForeColor.RGB = RGB(0, 176, 80).
- 5. Save and run the macro: Save the file as a macro-enabled workbook (.xlsm) and click each copied button to test.
Frequently Asked Questions
What exactly does Application.Caller return when a shape button runs a macro?
It returns the name of the shape that called the macro, so ActiveSheet.Shapes(Application.Caller) gives you that exact button object.
Does Application.Caller work in WPS Spreadsheets?
Yes. In WPS Spreadsheets, open the VBA editor from Developer > Visual Basic and use Application.Caller in a standard module assigned to a shape.
Why does my macro fail after I copy buttons to another worksheet?
Copied buttons often get new shape names or indexes, so fixed references like Shapes("Button 1") point to the wrong shape or nothing. Application.Caller avoids that problem.
Can I use Application.Caller with ActiveX command buttons?
Application.Caller is mainly used with Form control buttons or shapes assigned to macros. ActiveX command buttons are handled through their own event procedures.
