excel checkboxes control

Question

I'm a complete beginner in VBA.

I created several checkboxes in excel (not active X) and I would like to do particular kind of control: I have checkboxes named A, B, C and D I would like to do one (or more) VBA macro that would do : If B is checked/unchecked then C and D are checked/unchecked If A is checked/unchecked then B,C and D are checked/unchecked

I tried the following for the whole control :

    Sub BoucleCheckBoxes_Formulaire()
    Dim Cb As CheckBox
 
    'Boucle sur les checkboxes formulaires de la Feuil1
    For Each Cb In ActiveSheet.CheckBoxes
        'si la case est cochée
        '(utilisez xlOff pour vérifier le statut "décoché")
        If Cb.Value = xlOn Then
            Cb.Value = xlOff
        Else
            If Cb.Value = xlOff Then
                Cb.Value = xlOn
            End If
        End If
    Next Cb
End Sub

But I have a problem: The checkbox linked to the macro is always checked (though on each click the other checkboxes state toggle as expected), probably because it's included in the loop as the other checkboxes. In fact I would like to be able to apply the checkbox state to a determined and limited list of other checkboxes

I couldn't find if it is possible to select a group of checkboxes depending on their position or name

can anyone help, please

thank you

Here is the complete solution from the answer:

I create four checkboxes, Case 1, Case 2, Case 3, Case 4.

Case 1 controls the 3 others at once : when On => all on, when Off => all Off.

Each of the others can switch individually : when Off, Case 1 becomes Off, when On, if the other are On Case 1 switches On.

Here are the macros used to accomplish this:

Sub Case_1_Cliquer()

    c1 = ActiveSheet.CheckBoxes("Case 1").Value

    If c1 = xlOff Then
        ActiveSheet.CheckBoxes("Case 2").Value = xlOff
        ActiveSheet.CheckBoxes("Case 3").Value = xlOff
        ActiveSheet.CheckBoxes("Case 4").Value = xlOff
    ElseIf c1 = xlOn Then
        ActiveSheet.CheckBoxes("Case 2").Value = xlOn
        ActiveSheet.CheckBoxes("Case 3").Value = xlOn
        ActiveSheet.CheckBoxes("Case 4").Value = xlOn
    End If

End Sub

Sub Case2_Cliquer()
    c2 = ActiveSheet.CheckBoxes("Case 2").Value
    c3 = ActiveSheet.CheckBoxes("Case 3").Value
    c4 = ActiveSheet.CheckBoxes("Case 4").Value
    
    If c2 = xlOff Then
        ActiveSheet.CheckBoxes("Case 1").Value = xlOff
    End If
    
    If c2 = xlOn Then
        If ((c3 = xlOn) And (c4 = xlOn)) Then
            ActiveSheet.CheckBoxes("Case 1").Value = xlOn
        End If
    End If
End Sub

Sub Case3_Cliquer()
    c2 = ActiveSheet.CheckBoxes("Case 2").Value
    c3 = ActiveSheet.CheckBoxes("Case 3").Value
    c4 = ActiveSheet.CheckBoxes("Case 4").Value
    
    If c3 = xlOff Then
        ActiveSheet.CheckBoxes("Case 1").Value = xlOff
    End If
    
    If c3 = xlOn Then
        If ((c2 = xlOn) And (c4 = xlOn)) Then
            ActiveSheet.CheckBoxes("Case 1").Value = xlOn
        End If
    End If
End Sub

Sub Case4_Cliquer()
    c2 = ActiveSheet.CheckBoxes("Case 2").Value
    c3 = ActiveSheet.CheckBoxes("Case 3").Value
    c4 = ActiveSheet.CheckBoxes("Case 4").Value
    
    If c4 = xlOff Then
        ActiveSheet.CheckBoxes("Case 1").Value = xlOff
    End If
    
    If c4 = xlOn Then
        If ((c3 = xlOn) And (c2 = xlOn)) Then
            ActiveSheet.CheckBoxes("Case 1").Value = xlOn
        End If
    End If
End Sub

usmanhaq usmanhaq · Accepted Answer · 2018-11-20T13:13:31

You can try this code; i recommend to test it in a new file and if it works, you can embed it in your actual file.

You can perform following steps

Create check boxes
Assign some appropriate names to them that would be used to reference them in vba
Right click each check box you want to be as a trigger and assign macros to them that would be called when they would get clicked

I have created 4 check boxes, named them CHB_A, CHB_B, CHB_C, and CHB_D

Following is a macro that you can assign to checkbox A and when it will be clicked this macro will be called

Sub CHB_A_Click()

    chb_a = ActiveSheet.CheckBoxes("CHB_A").Value

    If chb_a = xlOff Then
        ActiveSheet.CheckBoxes("CHB_B").Value = xlOff
        ActiveSheet.CheckBoxes("CHB_C").Value = xlOff
        ActiveSheet.CheckBoxes("CHB_D").Value = xlOff
    ElseIf chb_a = xlOn Then
        ActiveSheet.CheckBoxes("CHB_B").Value = xlOn
        ActiveSheet.CheckBoxes("CHB_C").Value = xlOn
        ActiveSheet.CheckBoxes("CHB_D").Value = xlOn
    End If

End Sub

i have tested your code, and it is toggling the state of all the checkboxes

For the statement of yours "The checkbox linked to the macro is always checked (though on each click the other checkboxes state toggle as expected)"

It is because if you click one and check/uncheck it, it will immediately get toggled again as the code is toggling state of all the check boxes. You will not see the difference.

excel checkboxes control

1 Answers