Userform - Make select field required for submission - 1 message containing all errors

[JustHooch]

I have 2 wants (1) to make only select fields required for submission on my userform (2) to have a single Message box that contains all the errors listed.

Userform details -

• Submission happens when the CommandButtonSubmit is clicked
• Required fields include textboxes, comboboxes and frames containing checkboxes where at least 1 selection must be made

The below is what I have worked out so far but it has some issues.

1. The code for "That one option is checked for Offer Contains" will check the frame and display a message is not checkboxes = true but will not stop the form from being submitted
2. The code for "That one option is checked for Offer Contains" is separate from the code "All other required fields", which means if both codes have blank fields then I get 2 separate message boxes
3. The code for "All other required fields" only gives the user the first fail message no matter how many fields are blanks

- So for the below, if the user has both OfferName and OfferType blank when they click on submit they will get a message that "Offer name is required" only
​

- They will enter the offer name, hit submit again only then will they get the message about the Offer Type
​

I would like to get all the required fields in one line of logic that will provide the user with a list of all the fields that are missing data in one message box. Is this possible?

'When Click Submit button
Private Sub CommandButtonSubmit_Click()


'Check for Required fields
    'That one option is checked for Offer Contains
        For Each ctrl In FrameContains.Controls
          If TypeOf ctrl Is msforms.CheckBox Then
               If ctrl.Value = True Then
                     F1D = True
                     Exit For
                     End If
               End If
           Next ctrl  
        If F1D = False Then
            MsgBox "No Option Selected for Offer Contains (Pick all that Apply)"
        End If
   
'All other required fields
        With Me[INDENT]If TextBoxOfferName.Value = "" Then[/INDENT]
             MsgBox "Offer Name is required"
                Exit Sub
            ElseIf ComboBoxOfferType = "" Then
                MsgBox "Offer Type is required"
                Exit Sub
            End If
        End With
    
'Make Ticket Tab active
    Sheets("Ticket Offer Info").Activate


'Transfer Segment Selections
    myVar = ""
    For x = 0 To Me.ListBoxSegment.ListCount - 1
        If Me.ListBoxSegment.Selected(x) Then
            If myVar = "" Then
                myVar = Me.ListBoxSegment.List(x, 0)
            Else
                myVar = myVar & "," & Me.ListBoxSegment.List(x, 0)
            End If
        End If
    Next x
    Range("B5").Value = myVar
    
'Transfer all other information to Ticket Tab
    Range("B3").Value = TextBoxOfferName[INDENT]'have lots more that feeds but took out to save space[/INDENT]


    Unload Me


End Sub

 

[Fluff]

How about

Private Sub CommandButtonSubmit_Click()
   Dim Msg As String
   Dim F1D As Boolean

'Check for Required fields
    'That one option is checked for Offer Contains
        For Each Ctrl In FrameContains.Controls
          If TypeOf Ctrl Is MSForms.CheckBox Then
               If Ctrl.Value = True Then
                     F1D = True
                     Exit For
                     End If
               End If
           Next Ctrl
        If F1D = False Then
            Msg = "No Option Selected for Offer Contains (Pick all that Apply)" & vbLf
        End If
   
'All other required fields
   With Me
      If .TextBoxOfferName.Value = "" Then Msg = Msg & "Offer Name is required" & vbLf
      If .ComboBoxOfferType = "" Then Msg = Msg & "Offer Type is required" & vbLf
   End With
   
   If Len(Msg) > 0 Then
      MsgBox Msg
      Exit Sub
   End If
'Make Ticket Tab active
    Sheets("Ticket Offer Info").Activate


'Transfer Segment Selections
    MyVar = ""
    For x = 0 To Me.ListBoxSegment.ListCount - 1
        If Me.ListBoxSegment.Selected(x) Then
            If MyVar = "" Then
                MyVar = Me.ListBoxSegment.List(x, 0)
            Else
                MyVar = MyVar & "," & Me.ListBoxSegment.List(x, 0)
            End If
        End If
    Next x
    Range("B5").Value = MyVar
    
'Transfer all other information to Ticket Tab
    Range("B3").Value = TextBoxOfferName
'have lots more that feeds but took out to save space


    Unload Me


End Sub

 

[JustHooch]

That worked perfectly!. Thank you very much for the help.

 

[Fluff]

Glad to help & thanks for the feedback

 

[JustHooch]

Follow-up Question:
Can you make the "That one option is checked for Offer Contains" code last? I moved it but then that particular string no longer worked. It isn't critical but it's just the last field on the form so it would make more sense for that message to be last in the list.

 

[Fluff]

Try

   Dim Msg As String
   Dim F1D As Boolean

   
'All other required fields
   With Me
      If .TextBoxOfferName.Value = "" Then Msg = "Offer Name is required" & vbLf
      If .ComboBoxOfferType = "" Then Msg = Msg & "Offer Type is required" & vbLf
   End With
   
'Check for Required fields
    'That one option is checked for Offer Contains
        For Each Ctrl In FrameContains.Controls
          If TypeOf Ctrl Is MSForms.CheckBox Then
               If Ctrl.Value = True Then
                     F1D = True
                     Exit For
                     End If
               End If
           Next Ctrl
        If F1D = False Then
            Msg = Msg & "No Option Selected for Offer Contains (Pick all that Apply)"
        End If

   If Len(Msg) > 0 Then
      MsgBox Msg
      Exit Sub
   End If

 

[JustHooch]

That worked too. Thank you again.

 

[Fluff]

You're welcome

 

[JustHooch]

One more follow-up question. Again I tried modifying with no success I'm bating a 1000 today . I have another version of the same userform without the frame. So basically need the "All other required fields" validation only will all the messages together.

 

[Fluff]

In that case simply remove the code portion that looks at the frame.