How can I make a select/deselect all check box in VBA, that only works on certain checkboxes?-CodePudding

I have a dashboard with four interactive graphs. They function with check boxes with format control, so that checking e.g. the "USA" box changes a cell from TRUE to FALSE. The TRUE/FALSE statement then informs whether the USA data should be showed in the graph.

I have made a select/deselect all checkbox, however, when you press it, it selects/deselects all boxes in all four graphs.

I am looking for an edition to my macro, where I can specify which boxes each of the four select/deselect all boxes should affect.

This is the code I am using for my select/deselect checkbox:

Sub AllCheckboxes()

Dim cb As CheckBox

For Each cb In ActiveSheet.CheckBoxes

    If cb.Name <> ActiveSheet.CheckBoxes("Check Box 1").Name Then
        cb.Value = ActiveSheet.CheckBoxes("Check Box 1").Value
    End If
        
Next
        
End Sub

I have three of those named AllCheckboxes 2, 3, 4, and with different names (Check Box 1, 2, 3 and 4).

Hope you understand my issue!

Thanks in advance.

CodePudding user response：

You can use a single Sub for this - check the name of the calling checkbox and use that to identify the linked checkboxes to be set.

Sub AllCheckboxes()
    
    Dim arr, cb As Object, cbName
    
    'which checkbox was clicked, and which are its "linked" boxes?
    Select Case Application.Caller
        Case "Check Box 1": arr = Array("Check Box 2", "Check Box 3", _
                                        "Check Box 4", "Check Box 5")
        
        Case "Check Box 6": arr = Array("Check Box 7", "Check Box 8", _
                                        "Check Box 9", "Check Box 10")
    End Select
    
    If Not IsEmpty(arr) Then 'got a match?
        Set cb = ActiveSheet.CheckBoxes(Application.Caller) 'the calling checkbox
        For Each cbName In arr
            ActiveSheet.CheckBoxes(cbName).Value = cb.Value
        Next cbName
    End If
    
End Sub

As an alternative to using arrays, you could use a naming convention such that (eg) clicking "CheckBox_A" sets "CheckBox_A_1", "CheckBox_A_2", etc