Un check a large volume of checkboxes

[Kelethaar]

I am writing this little appliction in VBA (Excel, office 2k3) and i have added lots of checkboxes that will fill a textbox when the value returns true. below is an example... as you can see when this box returns a value of true, it sets the value of every other checkbox to false and displays appropriate text within a textbox (that will eventually fill a row with data). what i would like to accomplish is something less code intensive, as i'll have to write this much code (and possibly more as i add more features) for every checkbox in my user form. as you can imagine this is going to be extremely time consuming. is there way to uncheck all, or maybe a range of checkboxes without this much code, something along the thought of if a checkbox value is true then all other check boxes are false? I'd love to be able to streamline this if i could, if it's possible.

Thanks in advance,
Kelethaar

Private Sub ChkBK12_Click()
If ChkBK12.Value = True Then
ChkWH355.Value = False
ChkBK181.Value = False
Chk007.Value = False
ChkPB.Value = False
ChkWH01.Value = False
ChkBK08.Value = False
ChkRD03.Value = False
ChkGreen.Value = False
ChkHaze.Value = False
Txtcoat.Text = ""
Txtcoat.Text = ChkBK12.Caption
DoEvents
End If
If ChkBK12.Value = False Then
Txtcoat.Text = ""
DoEvents
End If

End Sub

 

[Datsmart]

You can uncheck all the checkboxes using this code:

Sub uncheck_all()
Dim sh As Shape
    For Each sh In ActiveSheet.Shapes
        'Uncheck ActiveX objects
        If sh.Type = msoOLEControlObject Then
            If TypeName(sh.OLEFormat.Object.Object) = "CheckBox" Then sh.OLEFormat.Object.Object = False
        End If
        'Uncheck Forms objects
        If sh.Type = msoFormControl Then
            If sh.FormControlType = xlCheckBox Then sh.OLEFormat.Object = False
        End If
    Next sh
End Sub

Then run some code to check just the ones you want.

Note: When run, this code will uncheck both Forms and ActiveX CheckBox types.

 

[Kelethaar]

This seems like the right idea, but i need control of just the active user form. for instance, if i activate checkbox1 within form1 (causing it to return a value of true) i want all of the other checkboxes in form one to immediately return a value of false (while maintaining a value of true for checkbox1). un-checking all acitvex controls or other controls outside of the current form could actually cause me somewhat of a headache.

I hope I am not being terribly difficult, and apologize if i am hard to understand.

Thank you, and I appreciate the feedback,

Kelethaar

 

[Norie]

Kelethaar

Have you considered using option buttons instead of checkboxes?

It sounds to me that that would do the job for you without a whole load of code.

If you have groups of checkboxes that are mutually exclusive that can easily be dealt with using option buttons by setting the groupname.

 

[Kelethaar]

well... I guess I should have tried playing with that in the first place. I was under the impression that an option button was in essence no different than a check box control...

you guys are great!

Thank you,
Kelethaar