Making ALL fields in a userform mandatory

Robert_Conklin

Board Regular
Joined
Jun 19, 2017
Messages
173
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Each userform will have to have its own code that checks each of its own controls.
That code could be very generic


Code:
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.
 
Upvote 0
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.
 
Upvote 0
Thanks Mike! That will work just fine. I just have to list each of my fields as a Case?
 
Upvote 0
Here is what I integrated. It still writes everything to another sheet and does not show the the error message. Any suggestions?

Code:
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
 
Upvote 0
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.


Code:
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"
 
Upvote 0
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.
 
Upvote 0
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:

Code:
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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