VBA & Macro Problems

How to Change Excel Shape Text and Color When Clicked Using VBA

Huma Ashraf Ch Oct 8, 2026 870 views

Question details

The user wants to dynamically change the text and fill color of a specific Excel shape to indicate a 'completed' status upon being clicked, while simultaneously resetting all other shapes to their default formatting.

How to Change Excel Shape Text and Color When Clicked in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating interactive dashboard buttons or visual task trackers where clicking a shape updates its visual state and resets the rest of the shapes in the group.
Observed behavior: Shapes need to dynamically update text and fill color based on user interaction using an assigned macro.

Before you start

Ensure that the Developer tab is enabled in your Excel ribbon and that your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) to allow VBA code to execute.

Solution 1Recommended

Create a VBA Macro to Toggle Shape Text and Color

Write a VBA procedure that loops through your worksheet shapes, changing the clicked shape's status and resetting the others.

By utilizing a centralized VBA procedure that accepts a shape number, you can efficiently manage the state of multiple shapes. The ActiveSheet.Shapes property allows the macro to run smoothly on whichever worksheet is currently active.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor. Click 'Insert' from the top menu and select 'Module' to create a new blank module.

Define the ColorShape Procedure

Create a Sub procedure named ColorShape that accepts an integer parameter (e.g., 'Sub ColorShape(shapeID As Integer)'). This parameter will represent the specific shape that was clicked.

Loop Through the Shapes

Write a For loop (e.g., 'For i = 1 To 4') to iterate through your shapes. Use 'ActiveSheet.Shapes("Shape" & i)' to reference each shape dynamically, ensuring your shapes are named 'Shape1', 'Shape2', etc., in your worksheet.

Apply Conditional Formatting

Inside the loop, use an 'If i = shapeID' statement. For the matching shape, set its '.Fill.ForeColor.RGB' to your desired highlight color and its '.TextFrame.Characters.Text' to 'Completed'. In the 'Else' block, reset these properties to the default color and original text.

Assign Macros to Your Shapes

Create individual wrapper macros (like Sub ClickShape1, which simply calls 'ColorShape 1'). Return to your Excel sheet, right-click each shape, select 'Assign Macro', and link each shape to its respective wrapper macro.

Create a VBA Macro to Toggle Shape Text and Color

Naming Shapes Properly: Ensure the names of the shapes on your worksheet match the naming convention in your VBA code exactly. You can check or change a shape's name using the Name Box located to the left of the formula bar.

WPS Office Solutions

Create Interactive Dashboards with WPS Spreadsheets

WPS Office provides robust support for VBA macros and Excel-compatible shapes, allowing you to build interactive spreadsheets and dashboards effortlessly.

1. Open the Developer Tab: Launch WPS Spreadsheets, navigate to the Developer tab on the ribbon, and click on the VBA Editor.
2. Insert Dashboard Shapes: Go to the Insert tab, select Shapes, and draw the buttons or visual trackers needed for your interactive project.
3. Assign the Macro: Right-click your newly created shape, select 'Assign Macro' from the context menu, and link it directly to your VBA procedure.

Fully compatible with Microsoft Excel .xlsm files and VBA macrosEasily insert, group, and format shapes for visual interactivitySeamlessly assign macros to objects with an intuitive interfaceLightweight, fast, and highly reliable for complex data tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why is my VBA macro not changing the shape color when clicked?

This usually happens if the shape names in your worksheet do not exactly match the names referenced in your VBA code (for example, 'Shape 1' vs 'Shape1'). Click on the shape and check the Name Box next to the formula bar to verify its exact name.

How do I find or change the name of a shape in Excel?

Click on the shape to select it, then look at the Name Box located immediately to the left of the formula bar. To change the name, simply type the new name into the Name Box and press Enter.

Can I use this shape-clicking macro on multiple worksheets?

Yes. By using the 'ActiveSheet.Shapes' method in your VBA code, the macro dynamically applies to the shapes on the currently active worksheet, rather than being restricted to a hardcoded sheet name.

How do I assign a VBA macro to a shape?

Right-click the shape you want to use as a button, select 'Assign Macro' from the context menu, choose your desired macro from the list that appears, and click OK.

Excel & Data Problems