Limiting number of checkboxes

[Bacardi84uk]

Firstly of all I want to say hello. I've been a regular lurker around the forum and found the content here invaluable. I've come across a problem that has required me to sign up and ask a question of my own now though. I did a forum search and found a similar topic but unfortunately there were no replies helping that guy out (https://www.mrexcel.com/forum/excel-questions/726720-limit-number-checkboxes-help-please.html)

My scenario is this: I have three groups of varying numbers of checkboxes. The user is to tick any 5 from each group, and these check boxes relate to values which will be passed to cells A:1 to C:5 upon clicking and OK command button; Group A checkboxes transpose to row A, group B to row B etc. Once five in a group are selected, the remaining checkboxes of said group should be disabled until one of them is unticked again.

I've grouped the checkboxes into three Frames. My methods of counting up the ticks so far count all checkboxes of the userform and I cannot seem to restrict it to those checkboxes contained within each grouping. I've had a go at putting the checkbox groups on individual Tabstrip and Multipage controls to no avail.

Could anyone help me out please?

 

[mikerickson]

Code like this should help. Note that the CoCheckBoxCount function allows you to use the GroupName to group checkboxes the same as OptionButtons.

Function CoCheckBoxCount(aCheckbox As MSForms.CheckBox) As Long
    Dim oneControl As MSForms.Control
    For Each oneControl In aCheckbox.Parent.Controls
        If TypeName(oneControl) = "CheckBox" Then
            If oneControl.Value And (oneControl.GroupName = aCheckbox.GroupName) Then
                CoCheckBoxCount = CoCheckBoxCount + 1
            End If
        End If
    Next oneControl
End Function

Sub LimitCheckBox(aCheckbox As MSForms.CheckBox)
    Dim maxChecked As Long
    maxChecked = 5
    If CoCheckBoxCount(aCheckbox) > maxChecked Then
        aCheckbox.Value = False
    End If
End Sub

Private Sub CheckBox1_Click()
    LimitCheckBox CheckBox1
End Sub

Private Sub CheckBox2_Click()
    Call LimitCheckBox(CheckBox2)
End Sub

Private Sub CheckBox3_Click()
    Call LimitCheckBox(CheckBox3)
End Sub

' ...

Ideally, this could be implemented in a Class Module form.

 

[Bacardi84uk]

Code like this should help. Note that the CoCheckBoxCount function allows you to use the GroupName to group checkboxes the same as OptionButtons.

 ...

Ideally, this could be implemented in a Class Module form.

Thanks! I had no idea checkboxes could utilise GroupName. That's helped a lot. Out of interest how would I go about setting the checkbox to ".Enabled = False" once the max have been reached? ".Value = False" works great to prevent further selection, but I wouldn't mind having them properly greyed out.

 

[mikerickson]

Perhaps

Sub LimitCheckBox(aCheckbox As MSForms.CheckBox)
    Dim maxChecked As Long
    maxChecked = 5
    If CoCheckBoxCount(aCheckbox) <= maxChecked Then
        For Each oneControl in aCheckBox.Parent.Controls
            If Typename(oneControl)= "Checkbox" Then
                If (Not(oneControl.Value)) And (oneControl.GroupName = aControl.GroupName) Then
                    oneControl.Enabled = (CoCheckBoxCount(aCheckbox) = maxChecked)
                End If
            End If
        Next oneControl
    End If
End Sub

 

[Bacardi84uk]

Thanks for the update but nope, it doesn't seem to do anything. I can keep clicking on checkboxes in the group forever when I replace the code with the new suggestion.