Originally Posted by
AlphaFrog
The suggested code effects checkboxes by Name. It was designed to work with Check Box 11 and then change Check Boxes 13, 14, and 15
Sub CheckBox1_Click()
Dim cb As CheckBox
Set cb = ActiveSheet.CheckBoxes("Check Box 2")
If ActiveSheet.CheckBoxes("Check Box 1").Value = xlOn Then
cb.Value = xlOff
cb.Enabled = False
cb.Interior.Color = vbRed
Else
cb.Enabled = True
cb.Interior.ColorIndex = xlNone
End If
End Sub
Thank you! This worked perfectly!
Edit: I am now trying to alter the code a bit so if I select the box it changes the color of multiple check boxes instead of just the 1 by adding in a line for the next check box, but I am unable to get more than one to change at a time. I am trying to do this by adding more ElseIf conditions and listing my changes for the other boxes I want to change.
Sub CheckBox169_Click()
Dim cb As CheckBox
Set cb = ActiveSheet.CheckBoxes("Check Box 178")
If ActiveSheet.CheckBoxes("Check Box 169").Value = xlOn Then
cb.Value = xlOff
cb.Enabled = False
cb.Interior.Color = vbRed
Set cb = ActiveSheet.CheckBoxes("Check Box 179")
ElseIf ActiveSheet.CheckBoxes("Check Box 169").Value = xlOn Then
cb.Value = xlOff
cb.Enabled = False
cb.Interior.Color = vbRed
Else
cb.Enabled = True
cb.Interior.ColorIndex = xlNone
End If
End Sub
I think my issue with this code is that i'm doing an ElseIf and I need it to to be an "and". So when the check box is selected it will change box 178 and 179. The code above is running, but it is only changing the color of box 178.
I have also tried this (But I get a type mismatch error when I try this):
Sub CheckBox169_Click()
Dim cb As CheckBox
Set cb = ActiveSheet.CheckBoxes("Check Box 178") And ("Check Box 179")
If ActiveSheet.CheckBoxes("Check Box 169").Value = xlOn Then
cb.Value = xlOff
cb.Enabled = False
cb.Interior.Color = vbRed
Else
cb.Enabled = True
cb.Interior.ColorIndex = xlNone
End If
End Sub
Bookmarks