Toggle Shape Fill & Text Colors

[konalion]

I've created a circle shape that I want to toggle on click, between a red fill (RGB=(192,0,0)) with white text, and white fill with grey text (RGB=(64,64,64)). I have the following code, but it only toggles the fill color between white and no fill, with no changes to the text. It appears to be using the forecolor instead of the backcolor, so I must be interpreting their usage incorrectly in this syntax. Any assistance or guidance is appreciated.


Sub circleMon_Click()

Dim circleMon As Shape
Set circleMon = ActiveSheet.Shapes(Application.Caller)

With circleMon.Fill
If .Visible = True Then
.ForeColor.RGB = RGB(64, 64, 64)
.BackColor.RGB = RGB(255, 255, 255)
.Visible = False
Else
.ForeColor.RGB = RGB(255, 255, 255)
.BackColor.RGB = RGB(192, 0, 0)
.Visible = True
End If
End With


End Sub

[Leith Ross]

Add a new VBA module to your workbook. Copy and paste the code below into it. Delete the code for your Click event and assign the new macro to the shape instead.



Sub circleMon()

Dim circleMon As Shape
Static status As Boolean

Set circleMon = ActiveSheet.Shapes(Application.Caller)
circleMon.Fill.Solid
circleMon.Fill.Transparency = 0

If status = True Then
circleMon.Fill.ForeColor.RGB = RGB(255, 0, 0)
circleMon.TextFrame.Characters.Font.Color = RGB(255, 255, 255)
status = False
Else
status = True
circleMon.Fill.ForeColor.RGB = RGB(255, 255, 255)
circleMon.TextFrame.Characters.Font.Color = RGB(64, 64, 64)
End If

End Sub

[konalion]

Worked wonderfully! Thank you so much. I only had to make one adjustment on color (255,0,0) to (192,0,0), otherwise flawless. Thanks again.

[Leith Ross]

Hello konalion,

You're welcome. I am going to post the updated macro I sent to you in a PM.

You can see the status by placing the formula "=GetStatus()" in the cell of your choosing. It will update automatically whenever the circle is clicked. In VBA, you can call GetStatus within a macro to return the current status, True or False.



Global status As Boolean

Function GetStatus() As Boolean
Application.Volatile
GetStatus = status
End Function

Sub TogglecircleMon()

Dim circleMon As Shape

Set circleMon = ActiveSheet.Shapes(Application.Caller)
circleMon.Fill.Solid
circleMon.Fill.Transparency = 0

If status = True Then
circleMon.Fill.ForeColor.RGB = RGB(255, 0, 0)
circleMon.TextFrame.Characters.Font.Color = RGB(255, 255, 255)
status = False
Else
status = True
circleMon.Fill.ForeColor.RGB = RGB(255, 255, 255)
circleMon.TextFrame.Characters.Font.Color = RGB(64, 64, 64)
End If

circleMon.Parent.Calculate

End Sub