Passing properties to a procedure

[Matt McQueen]

I've got a spreadsheet that uses "traffic lights" to
indicate the health of different parts of the business.
These traffic lights are just circles drawn with the
autoshapes option, and then given background colours of
green, orange and red.

I've written a simple macro that can be assigned to a
circle that changes the colour from green to orange to red
and back to green as you click on the circle. However,
I've got a lot of circles and I don't want to write
individual macros for each - I want to be able to assign
the same macro to each one.

How can I change my macro so that it realises that the
circle I want to change is the one I've just clicked on?
The steps I would want are that you click on the circle
and macro says - ah, you've clicked on "Oval X" and then
runs the code based on this selection. My current solution
which requires me to tell the macro which circle to change
(i.e. Shapes("Oval X").Select).

Regards,

Matt

 

[Chip Pearson]

Matt,

Application.Caller will return the name of the shape that called
the macro. Therefore, you can use code like

Sub ShapeClick()
Dim SH As Shape
Set SH = Worksheets(1).Shapes(Application.Caller)
MsgBox SH.Name
End Sub

Assign this macro to all the shape objects, and SH will contain a
reference to the shape that was clicked.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)