Dear Excel Users

I have several Shapes on a worksheet. I want the fill colour to toggle
between two values whenever the shape is clicked. I've assigned the
following Macro to a shape with the following code:

-------------------
Sub Macro1()

ActiveSheet.Shapes("AutoShape 1").Select
Call Toggle

End Sub


Sub Toggle()

If Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10 Then
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 17
Else
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
End If

Range("A1").Select 'so that the shape is "deselected"

End Sub
-------------------

The problem is that I need a new macro (based on Macro1) for EVERY
shape which calls the Toggle routine when clicked.

Can I assign just one Macro to ALL shapes which will toggle the colour
of the ONE shape I click on (i.e. is not specific to a given
shape/group)?

Many thanks

jw

Hi JW,

There is no need to select / de-select.

Drop Macro1, and change Toggle to:

Sub Toggle()
With ActiveSheet.Shapes(Application.Caller)
If .Fill.ForeColor.SchemeColor = 10 Then
.Fill.ForeColor.SchemeColor = 17
Else
.Fill.ForeColor.SchemeColor = 10
End If
End With
End Sub

---
Regards,
Norman


wrote in message
oups.com...
Dear Excel Users

I have several Shapes on a worksheet. I want the fill colour to toggle
between two values whenever the shape is clicked. I've assigned the
following Macro to a shape with the following code:

-------------------
Sub Macro1()

ActiveSheet.Shapes("AutoShape 1").Select
Call Toggle

End Sub


Sub Toggle()

If Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10 Then
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 17
Else
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
End If

Range("A1").Select 'so that the shape is "deselected"

End Sub
-------------------

The problem is that I need a new macro (based on Macro1) for EVERY
shape which calls the Toggle routine when clicked.

Can I assign just one Macro to ALL shapes which will toggle the colour
of the ONE shape I click on (i.e. is not specific to a given
shape/group)?

Many thanks

jw

Many thanks Norman, that works perfectly...BUT...!

....if I copy & paste a duplicate of one of the shapes, only the
original will toggle and not the copy.

(In fact I'm working with a Group composed of two AutoShapes - I
presume the reason for this problem is that a copied Group seems to
retain the same name as the original, whereas a copied Shape will take
a new name. When I manually change the name of the copied Group it
works fine. I wonder why the name isn't changed?)

Thanks again

Joel


Norman Jones wrote:
Hi JW,

There is no need to select / de-select.

Drop Macro1, and change Toggle to:

Sub Toggle()
With ActiveSheet.Shapes(Application.Caller)
If .Fill.ForeColor.SchemeColor = 10 Then
.Fill.ForeColor.SchemeColor = 17
Else
.Fill.ForeColor.SchemeColor = 10
End If
End With
End Sub

---
Regards,
Norman


wrote in message
oups.com...
Dear Excel Users

I have several Shapes on a worksheet. I want the fill colour to toggle
between two values whenever the shape is clicked. I've assigned the
following Macro to a shape with the following code:

-------------------
Sub Macro1()

ActiveSheet.Shapes("AutoShape 1").Select
Call Toggle

End Sub


Sub Toggle()

If Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10 Then
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 17
Else
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
End If

Range("A1").Select 'so that the shape is "deselected"

End Sub
-------------------

The problem is that I need a new macro (based on Macro1) for EVERY
shape which calls the Toggle routine when clicked.

Can I assign just one Macro to ALL shapes which will toggle the colour
of the ONE shape I click on (i.e. is not specific to a given
shape/group)?

Many thanks

jw