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?
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?