Un check a CheckBox when others are checked.

[Chuck]

Hi all, I am a newbie to Excel VB coding, any help would be greatly appreciated. I am trying to create some code that will uncheck a previously checked CheckBox. I have a worksheet with 8 CheckBoxes broken into two series (1-4) and (5-8). I am trying to get my code to work so that when CheckBox 1 is checked, any other checked CheckBox (2,3, or 4) becomes unchecked, likewise, if Checkbox 2 is checked, 1,3 and 4 become unchecked. The second series, 5-8, would be indentical to the previous one.

Here is the code I have been playing around with:


Sub CheckBox1_Click()
If CheckBox1.Value = True Then
CheckBox2.Value = False
CheckBox3.Value = False
CheckBox4.Value = False
Range("treat1") = "1"
Range("treat2") = "0"
Range("treat3") = "0"
Range("treat4") = "0"
End If
End Sub

Sub CheckBox2_Click()
If CheckBox2.Value = True Then
CheckBox1.Value = False
CheckBox3.Value = False
CheckBox4.Value = False
Range("treat2") = "1"
Range("treat1") = "0"
Range("treat3") = "0"
Range("treat4") = "0"
End If
End Sub

Sub CheckBox3_Click()
If CheckBox3.Value = True Then
CheckBox1.Value = False
CheckBox2.Value = False
CheckBox4.Value = False
Range("treat3") = "1"
Range("treat1") = "0"
Range("treat2") = "0"
Range("treat4") = "0"
End If
End Sub

Sub CheckBox4_Click()
If CheckBox4.Value = True Then
CheckBox2.Value = False
CheckBox3.Value = False
CheckBox1.Value = False
Range("treat4") = "1"
Range("treat2") = "0"
Range("treat3") = "0"
Range("treat1") = "0"
End If
End Sub

I always get the "Run-time error '424': Object required" error.

I am thinking that if I get the first series to work, I can just change the CheckBox numbers to get the rest to work. Any help would be greatly appreciated. Thanks in advance for whoever can help!

-Chuck.

[mdmackillop]

Why not use two groups of option buttons and their linked cell property. This is their intended purpose.

[Chuck]

Thanks mdmackillop,

I played with that too and I could not get it to work. That and the people I am making it for want CheckBoxes, however I am ok and tried the OptionButton method.

With the OptionButtons I cannot figure out how to separate it for two series, as of now, if I click one, all the others become unchecked or undoted. I tried linking the OptionButton to a cell and then a row of cells, but it treats both series as one. I tried using the code I provided above, but I get the same error. Essentially what I am trying to do is have two independent series. I just need the user to select one of four options A, B, C, or D for each series. Here is exaclty what I am trying to do:

A(0-3 Days) B(3-6D) C (6-9D) D(9-12D)
Project 1 Completion: [x] [ ] [ ] [ ]

Project 2 Completion: [ ] [x] [ ] [ ]

If you or anyone know of an easier solution, please do let me know! Thanks in advance for the help.

[mdmackillop]

Heres an option button sample

[mdmackillop]

OK, Just noticed the Grouping problem. I'll look into it

[tstav]

Ungroup them.
Change the GroupName property of the optionbuttons 1-4 to e.g. Group1.
Change the GroupName property of the optionbuttons 5-8 to e.g. Group2.

[mdmackillop]

Found it. You use the GroupName property of the OptionButton to form the groups.

[Chuck]

Thank you all, this has solved my problem.