Making ALL fields in a userform mandatory

[Robert_Conklin]

Is it possible to add code to an already existing script that encompasses ALL fields in the userform and makes them mandatory, or do I need to list each field separately in each form?

 

[mikerickson]

Each userform will have to have its own code that checks each of its own controls.
That code could be very generic

Dim oneControl as msforms.Control

For each oneControl in Me.Control
    Select Case TypeName(oneControl)
        Case "TextBox", "ComboBox", "ListBox"
            If oneControl.Value = vbNullString Then
                MsgBox "empty field in " & oneControl.Name
                Exit For
            End If
         Case Else
    End Select
Next oneControl

What you do when there is an empty field is up to you.

 

[Roxxien]

What I would recommend using mike's code it's to have an exit sub after you MsgBox to block the user from continuing. You can put that part at the start of your script.

 

[Robert_Conklin]

Thanks Mike! That will work just fine. I just have to list each of my fields as a Case?

 

[Robert_Conklin]

Thanks Rox! Great idea!

 

[Robert_Conklin]

Here is what I integrated. It still writes everything to another sheet and does not show the the error message. Any suggestions?

Option ExplicitDim rng As Range, fnd As Range
Dim Ctrl As Control
Dim iRow As Long, i As Long
Dim wsAE As Worksheet
Dim cell
Dim oneControl As msforms.Control
Public Sub FillFields()


For Each oneControl In Me.Control
    Select Case TypeName(oneControl)
        
        Case "T_id", "C_02", "T_02", "C_01", "T_04", "T_03", "T_12", "T_13", "C_05", "C_04", _
        "T_11", "C_03", "T_06", "T_05", "T_07", "T_10", "T_08", "T_14", "T_15", "T_16", "C_06", _
        "T_17", "C_07", "T_23", "T_24", "T_01", "T_25"
    
            If oneControl.Value = vbNullString Then
            MsgBox "Please Fill ALL Fields" & oneControl.Name
                    Exit For
                End If
            Case Else
        End Select
    Next oneControl
End Sub

 

[My Aswer Is This]

Here is a script I suggest:
If the control is empty or not selected it's highlighted Red. If the control is filled in or selected then the control is highlighted "Green" If any controls are highlighted red you will get a popup message.

Having a whole lot of control names listed in a message box would not help me but if the control is highlighted in red it would get my attention.

Private Sub CommandButton4_Click()
'New One
Dim ans As Long
For Each ctrl In Me.Controls
    Select Case True
        Case TypeOf ctrl Is MSForms.CheckBox, TypeOf ctrl Is MSForms.OptionButton
            Select Case ctrl.Value
                Case True
                    ctrl.BackColor = vbGreen
                Case Else
                    ctrl.BackColor = vbRed: ans = ans + 1
            End Select
        Case TypeOf ctrl Is MSForms.TextBox, TypeOf ctrl Is MSForms.ComboBox
            Select Case ctrl.Value
                Case vbNullString
                    ctrl.BackColor = vbRed: ans = ans + 1
                Case Else
                    ctrl.BackColor = vbGreen
            End Select
    End Select
Next ctrl
If ans > 0 Then MsgBox "Please enter data in the required fields.  Controls in Red need values", vbInformation, "Enter data"

 

[Robert_Conklin]

I get the concept behind the code and I love the color idea, but where do I define my control names so the script knows which fields are mandatory? Also, where in my code to I place it, as it is now, it still write all of the data to the worksheet without displaying the error message.

 

[My Aswer Is This]

You do not need to name the mandatory fields. It assumes all fields are mandatory. Why have a control in the Userform if it's not mandatory?

This script would go to the top of your current script so it runs first.
If it finds a empty control it gives you a message and ends the script like try using this:

Private Sub CommandButton4_Click()
'New One
Dim ans As Long
For Each ctrl In Me.Controls
    Select Case True
        Case TypeOf ctrl Is MSForms.CheckBox, TypeOf ctrl Is MSForms.OptionButton
            Select Case ctrl.Value
                Case True
                    ctrl.BackColor = vbGreen
                Case Else
                    ctrl.BackColor = vbRed: ans = ans + 1
            End Select
        Case TypeOf ctrl Is MSForms.TextBox, TypeOf ctrl Is MSForms.ComboBox
            Select Case ctrl.Value
                Case vbNullString
                    ctrl.BackColor = vbRed: ans = ans + 1
                Case Else
                    ctrl.BackColor = vbGreen
            End Select
    End Select
Next ctrl
If ans > 0 Then
MsgBox "Please enter data in the required fields.  Controls in Red need values", vbInformation, "Enter data"
Exit Sub
Else
MsgBox "Keep doing stuff here"
End If
End Sub

 

[mikerickson]

You don't list the control names in the Select Case, you list the Typenames of the controls (e.g. ListBox, TextBox, ComboBox, CommandButton,...)

Notice that "filled in" doesn't make sense for some kinds of controls (e.g. Labels, CommandButtons, CheckBoxes (?)) and they need to be handled differendly than a TextBox. (OptionButtons actually don't have to be checked if the userform opens with one (per group) already checked)