CODEWITHSUNDEEP
vbaexcel
Cylen
Cylen

Reputation: 119

Selecting/Deselecting all Checkboxes inside a GroupBox

I'm trying to build an Excel where the User selects from a range of checkboxes at the start (in a groupbox), each checkbox would reveal a new possible selection of checkboxes (either listbox or groupbox, whichever you think more appropriate) and then based on their final selections, generate/reveal the relevant tabs for them to read.

I'm having difficulty getting a Select/Deselect all button in there. This is what I'm trying so far, without much luck. If anyone could help I'd be very grateful. 

Sub Tickall()

Dim CB As CheckBox
Dim OB As OptionButton
If ActiveSheet.OB.GroupBox.GroupBox1.CheckBoxes("Check Box 21").Value Then
    For Each CB In ActiveSheet.OB.GroupBox1.GroupBox1.CheckBoxes
        If CB.name <> ActiveSheet.OB.GroupBox1.CheckBoxes("Check Box 21").name Then
            CB.Value = True
        End If
    Next CB
End If
End Sub

Upvotes: 2

Views: 886

Answers (2)

Dick Kusleika
Dick Kusleika

Reputation: 33165

GroupBoxes doesn't appear to have a Controls property to For Each. The only way I could see to determine if a CheckBox is within a GroupBox is to check if it's physically within it. I hope that's not the right answer and there's some more elegant way, but here's an example anyway

Public Sub TickAll()

    Dim gb As GroupBox
    Dim cb As CheckBox
    Dim cbAll As CheckBox

    Set gb = Sheet1.GroupBoxes(1)
    Set cbAll = Sheet1.CheckBoxes("Check Box 6")

    For Each cb In Sheet1.CheckBoxes
        If IsInGroupBox(cb, gb) Then
            cb.Value = cbAll.Value
        End If
    Next cb

End Sub

Public Function IsInGroupBox(ByRef cb As CheckBox, ByRef gb As GroupBox)

    IsInGroupBox = cb.Top > gb.Top And _
        cb.Top < gb.Top + gb.Height And _
        cb.Left > gb.Left And _
        cb.Left < gb.Left + gb.Width

End Function

Upvotes: 1

mrbungle
mrbungle

Reputation: 1931

Untested but I believe this is what you're looking for

ActiveSheet.CheckBoxes.Value = xlOff


ActiveSheet.CheckBoxes.Value = xlOn

Upvotes: 0

Related Questions