Tom’s Tutorials For Excel: Programming Multiple Drawing Shapes
Here’s how you can assign one macro to multiple drawing shapes on your worksheet, and have them all behave uniquely, depending on which shape was clicked.
In the pictured example, four Oval drawing shapes were placed on the worksheet, keeping their default names of “Oval 1”, “Oval 2”, “Oval 3”, and “Oval 4” but changing their captions to “Quarter 1” and so on.. The same macro (posted below) was assigned to each Oval shape. When an Oval is clicked, its color becomes red for easier identification, and the other 3 Oval shapes become yellow, using RGB. Also, depending on the column location of the clicked Oval, the associated range of cells is selected.
Sub OvalColor() Dim intOval As Integer With ActiveSheet For intOval = 1 To 4 .Shapes("Oval " & intOval).Fill.ForeColor.RGB = RGB(255, 255, 0) Next intOval With .Shapes(Application.Caller) .Fill.ForeColor.RGB = RGB(255, 0, 0) With .TopLeftCell Range(Cells(5, .Column), Cells(22, .Column + 2)).Select End With End With End With End Sub