VBA Excel CheckBox to select all of some specific Checkboxes on Sheet

1
votes

I have an Excel Sheet with two columns of CheckBoxes of which the first CheckBox of each is the "Master" Checkbox which toggles all other CheckBoxes.
I got the code from This Tutorial.

It worked fine until i copied the Code into the second Column.
When Activating the first or the Second "Master" CheckBox it activates ALL CheckBoxes.
The First "Master" CheckBox is called "MCB1" the Second is in a Copy of this Code (with another Sub Name) and is Called MCB2.

Here's my Code: 

Sub SelectAll_Read()
Dim CB As CheckBox
 For Each CB In ActiveSheet.CheckBoxes
  If CB.Name <> ActiveSheet.CheckBoxes("MCB1").Name Then
   CB.Value = ActiveSheet.CheckBoxes("MCB1").Value
  End If
 Next CB
End Sub

Sub Mixed_ReadState()
Dim CB As CheckBox
For Each CB In ActiveSheet.CheckBoxes
  If CB.Name <> ActiveSheet.CheckBoxes("MCB1").Name And CB.Value <> ActiveSheet.CheckBoxes("MCB1").Value And ActiveSheet.CheckBoxes("MCB1").Value <> 2 Then
    ActiveSheet.CheckBoxes("MCB1").Value = 2
Exit For
   Else
     ActiveSheet.CheckBoxes("MCB1").Value = CB.Value
  End If
Next CB
End Sub
2
vbaexcelcheckbox

2 Answers

1
votes

First of all you need to differenciate Checkboxes from Column 1 to those in Column 2.

For instance you could name follower checkboxes in Column 1 as MCB1.1, MCB1.2, MCB1.3 and so on. The same thing for checkboxes in column 2: MCB2.1, MCB2.2, MCB2.3...

"Master" checkboxes in column 1 and 2 could be named MCB1 and MCB2.

Then your code needs to be modified as follows: (Didn't have time to test it) 

Sub SelectAll_Read()
Dim CB As CheckBox
    For Each CB In ActiveSheet.CheckBoxes
        If CB.Name <> ActiveSheet.CheckBoxes("MCB1").Name And CB.Name <> ActiveSheet.CheckBoxes("MCB2").Name Then
            If Mid(CB.Name, 4, 1) = "1"
                CB.Value = ActiveSheet.CheckBoxes("MCB1").Value
            ElseIf Mid(CB.Name, 4, 1) = "2"
                CB.Value = ActiveSheet.CheckBoxes("MCB2").Value
            End If
        End If
    Next CB
End Sub

Sub Mixed_ReadState()
Dim CB As CheckBox
Dim i As String

    For Each CB In ActiveSheet.CheckBoxes
        i = Mid(CB.Name, 4, 1)
        If CB.Name <> ActiveSheet.CheckBoxes("MCB" & i).Name And CB.Value <> ActiveSheet.CheckBoxes("MCB" & i).Value And  ActiveSheet.CheckBoxes("MCB" & i).Value <> 2 Then
            ActiveSheet.CheckBoxes("MCB" & i).Value = 2
            Exit For
        Else
            ActiveSheet.CheckBoxes("MCB" & i).Value = CB.Value
        End If
    Next CB
End Sub
1
votes

This alternative method worked for me

Sub SelectAll_CHECK_BOX()
    Dim CB As CheckBox
    Dim CB1 As Range
        Set CB1 = ActiveSheet.Range("A1:A30")
    For Each CB In ActiveSheet.CheckBoxes
            If Not Intersect(CB.TopLeftCell, CB1) Is Nothing Then
                CB.Value = ActiveSheet.CheckBoxes("MCB1").Value
          End If
    Next CB
    Dim CB2 As Range
        Set CB2 = ActiveSheet.Range("B1:B30")
    For Each CB In ActiveSheet.CheckBoxes
            If Not Intersect(CB.TopLeftCell, CB2) Is Nothing Then
                CB.Value = ActiveSheet.CheckBoxes("MCB2").Value
            End If
    Next CB
End Sub

Sub Mixed_ReadState()
Dim CB As CheckBox
    For Each CB In ActiveSheet.CheckBoxes
        If CB.Name <> ActiveSheet.CheckBoxes("MCB1").Name And CB.Value <> ActiveSheet.CheckBoxes("MCB1").Value And ActiveSheet.CheckBoxes("MCB1").Value <> 2 Then
        ActiveSheet.CheckBoxes("MCB1").Value = 2
        Exit For
        Else
        ActiveSheet.CheckBoxes("MCB1").Value = CB.Value
        End If
    Next CB
End Sub` 



Sub Mixed_ReadState()
Dim CB As CheckBox
    For Each CB In ActiveSheet.CheckBoxes
        If CB.Name <> ActiveSheet.CheckBoxes("MCB2").Name And CB.Value <> ActiveSheet.CheckBoxes("MCB2").Value And ActiveSheet.CheckBoxes("MCB2").Value <> 2 Then
        ActiveSheet.CheckBoxes("MCB2").Value = 2
        Exit For
        Else
        ActiveSheet.CheckBoxes("MCB2").Value = CB.Value
        End If
    Next CB
End Sub