VBA to Select All Checkboxes FormControl

[Eddy2]

Hi
I have a code to select/deselect all checkboxes with one "master" checkbox. This code is for OLEObjects/Active X controls, and im having a hard time rewriting it to work with Form Controls checkboxes. Any idea?

Private Sub CheckBox1_Click()

    Dim i As Integer
     
    For i = 2 To 39
        ActiveSheet.OLEObjects("CheckBox" & i).Object.Value = ActiveSheet.CheckBox1.Value
    Next i


End Sub

 

[Rick Rothstein]

Try this code line...

ActiveSheet.CheckBoxes = False

 

[Eddy2]

Try this code line...

ActiveSheet.CheckBoxes = False

Any idea how to make this work with a range of check boxes? I tried this, but dosnt work:

Sub chbx2222_Click()


    Dim i As Long
    For i = 1 To 39
        ActiveSheet.CheckBoxes("CheckBox " & i).Value = ActiveSheet.CheckBox1.Value
    Next i
End Sub

 

[Rick Rothstein]

Any idea how to make this work with a range of check boxes? I tried this, but dosnt work:

I may have misunderstood the purpose of your non-working code in the first message. I assumed you are trying to uncheck all of the checkboxes on the worksheet... was the not correct? If my assumption was wrong, tell us (in words) exactly what you are trying to do with your checkboxes.

 

[Eddy2]

I may have misunderstood the purpose of your non-working code in the first message. I assumed you are trying to uncheck all of the checkboxes on the worksheet... was the not correct? If my assumption was wrong, tell us (in words) exactly what you are trying to do with your checkboxes.

Sorry I was a bit unclear in my first message.

This I what I am trying to do: one check box to check/uncheck a defined number off check boxes (not everyone in the sheet).
The first code I posted works for OLEObjects checkboxes. However, I have changed to form controls checkboxes (due to performance issue). Therefore, I have to rewrite the code to work with form controls checkboxes.

 

[Eddy2]

This Works:

Sub ytrewq_Click()
    Dim v As Variant
    With ActiveSheet
        v = .CheckBoxes(1).Value
        N = .CheckBoxes.Count
        For i = 2 To 10
            .CheckBoxes(i) = v
        Next i
    End With
End Sub

 

[Eddy2]

Alternatively:

Sub Ia_Click()


    For i = 41 To 78
         ActiveSheet.CheckBoxes(i).Value = ActiveSheet.CheckBoxes(40).Value
    Next i
End Sub