Forum Discussion

Ryan M's avatar
Ryan M
Copper Contributor
Jan 25, 2018

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 ...
excel
Macros and VBA
JKPieterse's avatar
JKPieterse
Silver Contributor
Jan 25, 2018

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
  • Shianne_813's avatar
    Shianne_813
    Copper Contributor
    Dec 05, 2022
    Hello, I need this solution for a project I am working on, but I am fairly new to excel and I am wondering if you can help me. In my excel document I have 24 check boxes. I am trying to make it so that if I click checkbox21, it will also select checkbox5. And If I click checkbox22, it will also select checkbox5. This code is what I need to do that I think but I am unsure of how to edit this code to suite my needs. I am also stuck on how to group and name the group of checkboxes.
    Thanks
  • Ryan M's avatar
    Ryan M
    Copper Contributor
    Jan 28, 2018

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

    • JKPieterse's avatar
      JKPieterse
      Silver Contributor
      Jan 29, 2018

      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

       

       

Resources