clicking a single checkbox that will then click a specific group of checkboxes within sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-148668%22%20slang%3D%22en-US%22%3Eclicking%20a%20single%20checkbox%20that%20will%20then%20click%20a%20specific%20group%20of%20checkboxes%20within%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-148668%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20am%20a%20rookie%20when%20it%20comes%20to%20excel%20stuff%2C%20but%20have%20come%20a%20long%20way%20in%20last%20week%20or%20two.%20I%20have%20been%20killing%20myself%20trying%20to%20figure%20this%20out.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20have%20a%20form%2Fspreadsheet%20that%20I%20am%20creating%20with%20multiple%20checkboxes.%20I%20have%20it%20set%20up%20so%20that%20the%20user%20can%20select%20multiple%20checkboxes%2C%20which%20then%20is%20linked%20to%20other%20cells%20with%20text.%20They%20pick%20what%20they%20want%2C%20text%20is%20spit%20out%20in%20another%20column.%20The%20text%20is%20concatenated%20to%20created%20a%20list%20separated%20by%20commas.%20It%20works%20pretty%20well.%20However%2C%20I%20want%20to%20add%20additional%20checkboxes%20that%20automatically%20select%20others.%20Like%20a%20quick%20selection%20option%20that%20autoselects%20a%20specific%20group%20of%20boxes%20(and%20performs%20the%20%3Dif%20C1%2C%20%22text%22%2C%20%22%22)%20for%20each%20of%20those%20in%20the%20group).%20These%20are%20arranged%20in%20a%20specific%20order%20when%20concatenated.%20So%20in%20order%20to%20avoid%20redundancy%20I%20want%20the%20master%20group%20box%20to%20trigger%20the%20specified%20boxes%20so%20the%20concatenated%20%22product%20makes%20sense....probably%20not%20explaining%20well.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20is%20kind%20of%20like%20a%20menu.%20You%20can%20choose%20any%20combination%20A%2C%20B%2C%20C%2C%20or%20D.%20You%20could%20also%20select%20X%2C%20which%20would%20be%20ABCD.%20But%20do%20it%20in%20one%20click.%20But%20I%20also%20want%20option%20to%20take%20away%20one.%20So%20user%20clicks%20X%2C%20but%20doesn't%20want%20C.%20They%20could%20click%20ABD.%20But%20I%20want%20them%20to%20be%20able%20to%20click%20X%2C%20then%20unclick%20C.%20There%20are%20many%2C%20many%20combinations%20(Like%20A%20through%20Z).%20I%20have%20found%20code%20to%20check%20and%20clear%20all%20checkboxes%2C%20which%20doesn't%20help.%20It%20is%20more%20like%20X%20would%20click%20group%20ABCD%2C%20Y%20would%20click%20EFGH%2C%20Z%20would%20click%20IJKL.%20If%20I%20want%20A%2C%20G%2C%20IJKL%2C%20then%20I%20would%20click%20A%2C%20G%2C%20Z.%20If%20I%20didn't%20want%20K%2C%20I%20would%20unclick%20that%20too%20(and%20I%20would%20home%20Z%20would%20unclick%20but%20IJL%20would%20stay).%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20this%20grouping%3F%20Dependent%20checkboxes%3F%20Using%20form%20controls%2C%20not%20activeX.%20I%20read%20something%20about%20mother%2Fdaugher%20stuff...Tried%20recording%20a%20macro%20buy%20clicking%20the%20%22groups%22%20a%20boxes%20and%20then%20assigning%20to%20master%20box%2C%20but%20not%20working.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWould%20appreciate%20any%20guidance.%20The%20project%20is%20pretty%20complex%20(at%20least%20for%20me)%20and%20hard%20to%20explain.%20Hoping%20above%20explanation%20makes%20some%20sense.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-148668%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-149747%22%20slang%3D%22en-US%22%3ERe%3A%20clicking%20a%20single%20checkbox%20that%20will%20then%20click%20a%20specific%20group%20of%20checkboxes%20within%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-149747%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20you%20attach%20this%20macro%20to%20the%20sub-checkboxes%20that%20should%20do%20it%3A%3C%2FP%3E%0A%3CPRE%3ESub%20cbxGrp_Sub_Click()%0A%20%20%20%20Dim%20oCbx%20As%20CheckBox%0A%20%20%20%20Dim%20sGrp%20As%20String%0A%20%20%20%20Dim%20lTrue%20As%20Long%0A%20%20%20%20Dim%20lFalse%20As%20Long%0A%20%20%20%20Dim%20lMixed%20As%20Long%0A%20%20%20%20'Checkbox%20values%3A%0A%20%20%20%20'1%20true%0A%20%20%20%20'-4146%20false%0A%20%20%20%20'2%20%20mixed%0A%0A%20%20%20%20sGrp%20%3D%20Application.Caller%0A%20%20%20%20sGrp%20%3D%20Split(sGrp%2C%20%22_%22)(1)%0A%20%20%20%20For%20Each%20oCbx%20In%20ActiveSheet.CheckBoxes%0A%20%20%20%20%20%20%20%20If%20oCbx.Name%20Like%20%22cbx_%22%20%26amp%3B%20sGrp%20%26amp%3B%20%22_*%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20oCbx.Value%20%3D%201%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20lTrue%20%3D%20lTrue%20%2B%201%0A%20%20%20%20%20%20%20%20%20%20%20%20ElseIf%20oCbx.Value%20%3D%20-4146%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20lFalse%20%3D%20lFalse%20%2B%201%0A%20%20%20%20%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20lMixed%20%3D%20lMixed%20%2B%201%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%0A%20%20%20%20With%20ActiveSheet.CheckBoxes(%22cbxGrp_%22%20%26amp%3B%20sGrp)%0A%20%20%20%20%20%20%20%20If%20lTrue%20%26gt%3B%200%20And%20lFalse%20%3D%200%20And%20lMixed%20%3D%200%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20'true%0A%20%20%20%20%20%20%20%20%20%20%20%20.Value%20%3D%201%0A%20%20%20%20%20%20%20%20ElseIf%20lTrue%20%26gt%3B%200%20And%20(lFalse%20%26gt%3B%200%20Or%20lMixed%20%26gt%3B%200)%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20.Value%20%3D%202%0A%20%20%20%20%20%20%20%20%20%20%20%20'mixed%0A%20%20%20%20%20%20%20%20ElseIf%20lFalse%20%26gt%3B%200%20And%20lTrue%20%3D%200%20And%20lMixed%20%3D%200%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20.Value%20%3D%20-4146%0A%20%20%20%20%20%20%20%20%20%20%20%20'false%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20With%0AEnd%20Sub%0A%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-149571%22%20slang%3D%22en-US%22%3ERe%3A%20clicking%20a%20single%20checkbox%20that%20will%20then%20click%20a%20specific%20group%20of%20checkboxes%20within%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-149571%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20Jan%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20is%20working%20nicely.%20At%20first%20I%20was%20trying%20to%20group%20the%20checkboxes%2C%20but%20I%20get%20now%20that%20you%20leave%20them%20standing%20alone%20and%20only%20apply%20macro%20to%20the%20%22quick%20pick%22%20group%20box%20and%20that%20the%20%22sub%22%20checkboxes%20must%20have%20sequential%20numbers%20to%20work.%20Thank%20you!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%20here%20is%20another%20question.%20Can%20I%20make%20the%20%22quick%20pick%22%20group%20check%20box%20change%20from%20checked%20to%20mixed%20when%20one%20of%20the%20cbxGrp_1%20%22sub%22%20checkboxes%20is%20unchecked%3F%20I%20want%20the%20cbxGrp%20checkbox%20to%20stay%26nbsp%3Bselected%26nbsp%3Bif%20things%20are%20added%20(and%20none%20of%20the%20%22sub%22%20checkboxes%20is%20unchecked%20-%20as%20it%20is%20acting%20now).%20I%20don't%20want%20it%20to%20change%20to%20unchecked%20if%20one%20of%20the%20%22sub%22%20cbx%20is%20unchecked%2C%20since%20this%20will%20uncheck%20all%20the%20%22sub%22%20checkboxes.%20I%20just%20want%20it%20to%20go%20to%20mixed%20to%20imply%20to%20user%20they%20have%20deviated%20from%20the%20%22standard%20group%22.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-148822%22%20slang%3D%22en-US%22%3ERe%3A%20clicking%20a%20single%20checkbox%20that%20will%20then%20click%20a%20specific%20group%20of%20checkboxes%20within%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-148822%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20you%20name%20your%20controls%20smartly%20this%20can%20be%20done%20with%20a%20bit%20of%20VBA.%3C%2FP%3E%0A%3CP%3EName%20the%20checkboxes%20within%20a%20group%20cbx_1_1%2C%20cbx_1_2%2C%20cbx_1_3%2C%20cbx_1_4%3C%2FP%3E%0A%3CP%3EName%20each%20group%20checkbox%3A%20cbxGrp_1%2C%20cbxGrp_2%2C%20...%3C%2FP%3E%0A%3CP%3EAttach%20this%20macro%20to%20each%20group%20checkbox%3A%3C%2FP%3E%0A%3CPRE%3ESub%20cbxGrp_Click()%0A%20%20%20%20Dim%20oCbx%20As%20CheckBox%0A%20%20%20%20Dim%20lValue%20As%20Long%0A%20%20%20%20Dim%20sGrp%20As%20String%0A%20%20%20%20sGrp%20%3D%20Application.Caller%0A%20%20%20%20lValue%20%3D%20ActiveSheet.CheckBoxes(sGrp).Value%0A%20%20%20%20sGrp%20%3D%20Split(sGrp%2C%20%22_%22)(1)%0A%20%20%20%20For%20Each%20oCbx%20In%20ActiveSheet.CheckBoxes%0A%20%20%20%20%20%20%20%20If%20oCbx.Name%20Like%20%22cbx_%22%20%26amp%3B%20sGrp%20%26amp%3B%20%22_*%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20oCbx.Value%20%3D%20lValue%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%0AEnd%20Sub%0A%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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 with multiple checkboxes. I have it set up so that the user can select multiple checkboxes, which then is linked to other cells with text. They pick what they want, text is spit out in another column. The text is concatenated to created a list separated by commas. It works pretty well. However, I want to add additional checkboxes that automatically select others. Like a quick selection option that autoselects a specific group of boxes (and performs the =if C1, "text", "") for each of those in the group). These are arranged in a specific order when concatenated. So in order to avoid redundancy I want the master group box to trigger the specified boxes so the concatenated "product makes sense....probably not explaining well. 

 

It is kind of like a menu. You can choose any combination A, B, C, or D. You could also select X, which would be ABCD. But do it in one click. But I also want option to take away one. So user clicks X, but doesn't want C. They could click ABD. But I want them to be able to click X, then unclick C. There are many, many combinations (Like A through Z). I have found code to check and clear all checkboxes, which doesn't help. It is more like X would click group ABCD, Y would click EFGH, Z would click IJKL. If I want A, G, IJKL, then I would click A, G, Z. If I didn't want K, I would unclick that too (and I would home Z would unclick but IJL would stay). 

 

Is this grouping? Dependent checkboxes? Using form controls, not activeX. I read something about mother/daugher stuff...Tried recording a macro buy clicking the "groups" a boxes and then assigning to master box, but not working. 

 

Would appreciate any guidance. The project is pretty complex (at least for me) and hard to explain. Hoping above explanation makes some sense. 

3 Replies
Highlighted

If you name your controls smartly this can be done with a bit of VBA.

Name the checkboxes within a group cbx_1_1, cbx_1_2, cbx_1_3, cbx_1_4

Name each group checkbox: cbxGrp_1, cbxGrp_2, ...

Attach this macro to each group checkbox:

Sub cbxGrp_Click()
    Dim oCbx As CheckBox
    Dim lValue As Long
    Dim sGrp As String
    sGrp = Application.Caller
    lValue = ActiveSheet.CheckBoxes(sGrp).Value
    sGrp = Split(sGrp, "_")(1)
    For Each oCbx In ActiveSheet.CheckBoxes
        If oCbx.Name Like "cbx_" & sGrp & "_*" Then
            oCbx.Value = lValue
        End If
    Next
End Sub
Highlighted

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". 

Highlighted

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