Counter in Userform

[wpryan]

Hello,

I have a Userform that contains 3 option groups and a submit button. The first option group has three items, the second has two items, and the third has two items.

I want to enforce completion of all groups, and was thinking to do so by adding a counter to the form. When the user selects one item from the first group it is incremented by one, and so on for groups 2 and 3. Of course I would have to add some logic in the case that someone selects item1 from a group, then changes their mind and selects another item. Hitting the Submit button will compare the sum of the counter to the expected number of selections (in this case, 3), and either complete the submission of the data or kick out a message box, informing the user of their error and re-setting the form.

....any suggestions about how to approach this? The counter issue, I mean...

Thanks in advance.

 

[Jaafar Tribak]

Since you have 3 options groups, the total counter is obviously 3 as option buttons are mutually exclusive.
You can do something along these lines :

In the UserFprm Module:

Option Explicit

Private Sub SubmitButton_Click()
    If AllOptionsSelected(Me) Then
        ' continue with your code here ...
    Else
        MsgBox "some option(s) have not been selected.", vbCritical
    End If
End Sub

Private Function AllOptionsSelected(ByVal Frm As UserForm) As Boolean

    Const GroupCount = 3  '<< change as required
    Dim oCtrl As Control, lCounter As Long
  
    For Each oCtrl In Frm.Controls
        If TypeOf oCtrl Is MSForms.OptionButton Then
            If oCtrl.Value Then lCounter = lCounter + 1
            If lCounter = GroupCount Then AllOptionsSelected = True: Exit Function
        End If
    Next oCtrl

End Function

 

[wpryan]

In the meantime, I was able to cobble together something:

Counter = 0
    If (opt_C1.Value = True Or opt_C2.Value = True Or opt_C4.Value = True) Then
        counter = counter + 1
    End If
    If (opt_PSV1.Value = True Or opt_PSV2.Value = True) Then
        counter = counter + 1
    End If
    If (opt_Z8.Value = True Or opt_Z8_Neo.Value = True) Then
        counter = counter + 1
    End If
        
    If counter <> 3 Then
        MsgBox "Please complete all required data (Laser Cavity type, Power Sensor type, System type)!", vbOKOnly + vbInformation, "Incomplete Data Entry"
        Range("D41").Value = ""
        frmPSConfig.Show
    End If

Thanks for your efforts!

 

[Jaafar Tribak]

If you have that code in the UserForm module, you don't need the line : frmPSConfig.Show as it is already shown.