Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: Making ALL fields in a userform mandatory

  1. #1
    Board Regular
    Join Date
    Jun 2017
    Posts
    110
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Making ALL fields in a userform mandatory

    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?

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,936
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

    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.

  3. #3
    Board Regular
    Join Date
    Jul 2017
    Location
    Sherbrooke, Canada
    Posts
    289
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

    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.
    Why would I do more than 1 time the same thing, when I can code it?
    I'm from the Quebec, so don't mind English error.

    Code tag always help to read correctly some code.

  4. #4
    Board Regular
    Join Date
    Jun 2017
    Posts
    110
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

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

  5. #5
    Board Regular
    Join Date
    Jun 2017
    Posts
    110
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

    Thanks Rox! Great idea!

  6. #6
    Board Regular
    Join Date
    Jun 2017
    Posts
    110
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

    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

  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    13,542
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

    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"
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  8. #8
    Board Regular
    Join Date
    Jun 2017
    Posts
    110
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

    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.

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    13,542
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

    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
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  10. #10
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,936
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

    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)

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •