Forum Discussion
clicking a single checkbox that will then click a specific group of checkboxes within sheet
So I am a rookie when it comes to excel stuff, but have come a long way in last week or two. I have been killing myself trying to figure this out.
I have a form/spreadsheet that I am creating ...
Thank you Jan
This is working nicely. At first I was trying to group the checkboxes, but I get now that you leave them standing alone and only apply macro to the "quick pick" group box and that the "sub" checkboxes must have sequential numbers to work. Thank you!
Now here is another question. Can I make the "quick pick" group check box change from checked to mixed when one of the cbxGrp_1 "sub" checkboxes is unchecked? I want the cbxGrp checkbox to stay selected if things are added (and none of the "sub" checkboxes is unchecked - as it is acting now). I don't want it to change to unchecked if one of the "sub" cbx is unchecked, since this will uncheck all the "sub" checkboxes. I just want it to go to mixed to imply to user they have deviated from the "standard group".
If you attach this macro to the sub-checkboxes that should do it:
Sub cbxGrp_Sub_Click()
Dim oCbx As CheckBox
Dim sGrp As String
Dim lTrue As Long
Dim lFalse As Long
Dim lMixed As Long
'Checkbox values:
'1 true
'-4146 false
'2 mixed
sGrp = Application.Caller
sGrp = Split(sGrp, "_")(1)
For Each oCbx In ActiveSheet.CheckBoxes
If oCbx.Name Like "cbx_" & sGrp & "_*" Then
If oCbx.Value = 1 Then
lTrue = lTrue + 1
ElseIf oCbx.Value = -4146 Then
lFalse = lFalse + 1
Else
lMixed = lMixed + 1
End If
End If
Next
With ActiveSheet.CheckBoxes("cbxGrp_" & sGrp)
If lTrue > 0 And lFalse = 0 And lMixed = 0 Then
'true
.Value = 1
ElseIf lTrue > 0 And (lFalse > 0 Or lMixed > 0) Then
.Value = 2
'mixed
ElseIf lFalse > 0 And lTrue = 0 And lMixed = 0 Then
.Value = -4146
'false
End If
End With
End Sub
