PDA

View Full Version : Toggle Shape Text & Colour On Click - Application.Caller



BrI
05-15-2017, 08:03 AM
Making a spreadsheet that will have many shapes. I want to toggle the colour in Ovals when they are clicked and both the colour and text Rectangles.

Just discovered the Application.Caller property and reliable and works great when I just toggle the colour in the Ovals, working code is below:



Sub Ovals()
With ActiveSheet.Shapes(Application.Caller) 'Working for Ovals

If .Fill.ForeColor.RGB = vbGreen Then
.Fill.ForeColor.RGB = vbRed

ElseIf .Fill.ForeColor.RGB = vbRed Then
.Fill.ForeColor.RGB = vbGreen
End If

End With
End Sub

BUT, cannot get working for Rectangles where I want to toggle both the colour and text. Code below will work sometimes and sometimes not, mostly not working.


Sub rectangle()
With ActiveSheet.Shapes(Application.Caller) 'Rectangles - code not working

If .TextFrame.Characters.Text = "YES" Then
.TextFrame.Characters.Text = "NO"
.Fill.ForeColor.RGB = RGB(225, 244, 255) 'Light Green

ElseIf .TextFrame.Characters.Text = "NO" Then
.TextFrame.Characters.Text = "YES"
.Fill.ForeColor.RGB = RGB(153, 255, 153) 'Light Blue
End If

End With
End Sub


Any ideas on how to fix?

BrI
05-15-2017, 08:12 AM
Actually (now that I have posted!) this now seems to be working OK.

But, maybe there is better syntax etc. that could be used. Any help appreciated.