Counter in Userform

wpryan

Well-known Member
Joined
May 26, 2009
Messages
534
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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:
VBA Code:
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
 
Upvote 0
In the meantime, I was able to cobble together something:

VBA Code:
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!
 
Upvote 0
If you have that code in the UserForm module, you don't need the line : frmPSConfig.Show as it is already shown.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top