Two sets of checkboxes on one worksheet

[xenedra13]

I have two sets of checkboxes on the same worksheet. Previously, I have used this code to make a box that will select all checkboxes.

Sub CheckBox8_Click()
Dim CB As CheckBox
    For Each CB In ActiveSheet.CheckBoxes
        If CB.name <> ActiveSheet.CheckBoxes("Check Box 8").name Then
        CB.Value = ActiveSheet.CheckBoxes("Check Box 8").Value
        End If
    Next CB
End Sub
Sub Mixed_State8()
Dim CB As CheckBox
    For Each CB In ActiveSheet.CheckBoxes
        If CB.name <> ActiveSheet.CheckBoxes("Check Box 8").name And CB.Value <> ActiveSheet.CheckBoxes("Check Box 8").Value And ActiveSheet.CheckBoxes("Check Box 8").Value <> 2 Then
        ActiveSheet.CheckBoxes("Check Box 8").Value = 2
        Exit For
        Else
        ActiveSheet.CheckBoxes("Check Box 8").Value = CB.Value
        End If
    Next CB
End Sub

But this code does the same thing for all the checkboxes. I need to be able to separate the two sets of checkboxes somehow without having to rename every single checkbox. I tried changing "ActiveSheet" in this line

For Each CB In ActiveSheet.CheckBoxes

to a range value. And I tried adding the range after "ActiveSheet" and both of those gave me an error that said this object or method is not supported. Can someone show me how to change these macros so that I can have two sets of checkboxes that each have a checkbox that selects all in that set? Thanks in advance!

 

[Snakehips]

xenedra13,

Assuming that you have two groups of 8 checkboxes where names are 'Check Box 1' to 'Check Box 8' for one group and 'Check Box 9' to 'Check Box 16' for the other and that 8 and 16 are the 'controlling' check boxes.

Try revising the code in this fashion...

Sub CheckBox8_Click()
Dim i As Integer
   For i = 1 To 7
        ActiveSheet.CheckBoxes("Check Box " & i).Value = ActiveSheet.CheckBoxes("Check Box 8").Value
    Next i
End Sub






Sub CheckBox16_Click()
Dim i As Integer
   For i = 9 To 15
        ActiveSheet.CheckBoxes("Check Box " & i).Value = ActiveSheet.CheckBoxes("Check Box 16").Value
    Next i
End Sub




Sub Mixed_State8()
Dim i As Integer
Dim CB As CheckBox
    For i = 1 To 7
    Set CB = ActiveSheet.CheckBoxes("Check Box " & i)
        If CB.Name <> ActiveSheet.CheckBoxes("Check Box 8").Name And CB.Value <> ActiveSheet.CheckBoxes("Check Box 8").Value And ActiveSheet.CheckBoxes("Check Box 8").Value <> 2 Then
        ActiveSheet.CheckBoxes("Check Box 8").Value = 2
        Exit For
        Else
        ActiveSheet.CheckBoxes("Check Box 8").Value = CB.Value
        End If
    Next i
End Sub




Sub Mixed_State16()
Dim i As Integer
Dim CB As CheckBox
    For i = 9 To 16
    Set CB = ActiveSheet.CheckBoxes("Check Box " & i)
        If CB.Name <> ActiveSheet.CheckBoxes("Check Box 16").Name And CB.Value <> ActiveSheet.CheckBoxes("Check Box 16").Value And ActiveSheet.CheckBoxes("Check Box 16").Value <> 2 Then
        ActiveSheet.CheckBoxes("Check Box 16").Value = 2
        Exit For
        Else
        ActiveSheet.CheckBoxes("Check Box 16").Value = CB.Value
        End If
    Next i
End Sub

Hope that helps.

 

[xenedra13]

I thought this would work perfectly, but when I ran it I got a run-time error '1004' Unable to get the Checkboxes property of the worksheet class on the marked line.

Sub CheckBoxRun_Click()
Dim i As Integer
   For i = 56 To 67
        ActiveSheet.CheckBoxes("Check Box " & i).Value = ActiveSheet.CheckBoxes("Check Box 56").Value             'ERROR 1004
    Next i
End Sub

Any suggestions on how to fix this?