Maximum 2 checkboxes to be checekd in a group

[Holy Inquisitor]

I managed to come up with the following code for a checkbox group including 5 checkboxes.

Although it limits the possible selections to maximum 2, when 2 checkboxes are already selected it not always disables all other unselected checkboxes and in this case one of the previously selected checkboxes will become unchecked.

Can anyone help me how to make the code more accurate.

Thank you



Private Sub TwoPicksOnly(sGroup As String, sName As String)

Dim ole As OLEObject
Dim CheckedBoxCount As Integer
CheckedBoxCount = 0

For Each ole In Me.OLEObjects
If TypeName(ole.Object) = "CheckBox" And ole.Object.GroupName = sGroup And ole.Object.Value = True Then CheckedBoxCount = CheckedBoxCount + 1
If CheckedBoxCount > 2 And ole.Object.GroupName = sGroup Then ole.Object.Value = False And ole.Object.Enabled = False
Next ole

End Sub

 

[jamcall]

You need to make two iterrations thru the checkboxes - one to see how many are TRUE and the second to disable/enable as required. Recommend using the click event for the checkbox so as it is check/unchecked the validation is performed.

Private Sub CheckBox1_Click()
   TwoPicksOnly CheckBox1
End Sub
Private Sub CheckBox2_Click()
    TwoPicksOnly CheckBox2
End Sub
' Continue with these as needed

Private Sub TwoPicksOnly(chkbox)
    Dim ole As OLEObject
    Dim CheckedBoxCount As Integer
    Dim sGroup As String
    CheckedBoxCount = 0
    sGroup = chkbox.GroupName
    For Each ole In Me.OLEObjects
        If TypeName(ole.Object) = "CheckBox" Then
            If ole.Object.GroupName = sGroup _
                And ole.Object.Value = True Then
                    CheckedBoxCount = CheckedBoxCount + 1
            End If
        End If
    Next ole
    
    For Each ole In Me.OLEObjects
        If TypeName(ole.Object) = "CheckBox" Then
            If ole.Object.GroupName = sGroup _
                And ole.Object.Value = False Then
                    If CheckedBoxCount = 2 Then
                        ole.Object.Enabled = False
                    Else
                        ole.Object.Enabled = True
                    End If
            End If
        End If
    Next ole
End Sub

HTH,
~ Jim