Fix VBA Macro Coloring All Shapes Instead of Selected Shape on Mac
Question details
The user needs a VBA macro to fill only the currently selected shape with a specific color on macOS, rather than coloring all shapes that share a similar naming convention.
- Product
- Spreadsheets
- Device & OS
- macOS
- Scenario
- Executing a VBA script to dynamically change the fill color of a specific clicked or selected shape (e.g., an activity tracker or custom calendar block).
- Observed behavior
- The current VBA code loops through all shapes containing a specific string (like "CalActivity") in their name, applying the color change to every matching shape instead of just the active one.
Ensure you have enabled the Developer tab in your spreadsheet application and saved a backup copy of your macro-enabled workbook (.xlsm) before modifying the VBA code.
Remove the Loop and Reference the Selected Shape Directly
Instead of looping through all shapes in the worksheet, identify and target the specific shape directly using its unique name or Application.Caller.
When you use a 'For Each' loop to check shape names, VBA will apply changes to every shape that meets the text criteria. To fix this, you must construct the exact name of the clicked shape or capture it using Application.Caller, then apply the .Fill.ForeColor.RGB property exclusively to that individual object.
Press Option + F11 on your Mac keyboard to open the Visual Basic for Applications (VBA) editor. Locate the module containing your shape-coloring macro.
Find and delete or comment out the 'For Each shp In ActiveSheet.Shapes' loop that is checking if the shape name contains 'CalActivity'.
Use a direct With statement to reference the intended shape. For example: With ActiveSheet.Shapes("CalActivity" & ActivityID).
Add a condition to verify the shape type and set the RGB color. Example code: If .Type = msoShapeRectangle Or .Type = msoShapeOval Then .Fill.ForeColor.RGB = SelectColor. Close the block with End If and End With.
Write and Execute VBA Macros Flawlessly with WPS Spreadsheet
WPS Office provides comprehensive support for VBA and macros, allowing you to easily automate tasks, manage shape properties, and run advanced scripts with seamless compatibility.
- 1. Download and Install WPS Office: Get the free WPS Office suite from the official website and install it on your Mac.
- 2. Open your Macro-Enabled Workbook: Launch WPS Spreadsheet and open your .xlsm file containing the shape-targeting macros.
- 3. Access the VBA Editor: Go to the Developer tab and click on 'Visual Basic' to edit your shape-targeting macro directly.
- 4. Run and Test the Macro: Click on your customized shapes in the spreadsheet to see the corrected VBA macro apply colors instantly to the selected shapes.
Frequently Asked Questions
Why does my VBA macro change the color of all shapes instead of just one?
This happens because the macro uses a For Each loop to iterate through all shapes on the sheet. If the condition matches a shared naming pattern, it applies the color to every shape that fits the pattern instead of limiting it to the active selection.
How can I get the name of the shape I just clicked in VBA?
You can use Application.Caller in your VBA script. When a macro is assigned to a shape, Application.Caller automatically returns the name of the specific shape that was clicked to trigger the macro.
Can I change the color of a specific shape type only?
Yes, you can check the .Type property of the shape before changing its color. For example, checking 'If .Type = msoShapeRectangle' ensures only rectangular shapes are affected by the script.
Are Excel VBA macros containing shape properties compatible with WPS Office on Mac?
Yes, WPS Spreadsheet natively supports VBA, including shape objects and their .Fill.ForeColor.RGB properties. Most macros written for Excel will run seamlessly in WPS Office.
