Userform: Required Fields on Active Multipage Only

    Hello everyone,


    I can't wait until I am one of the experts answering these questions instead of one of the novice members posting them!


    I have a Userform that contains a Multipage object. I've coded the Multipage so that the page that is active is linked to the selection in a combobox using the ListIndex property. And I have 6 fields in the top (not on the multipage) that are common to each submission that are required and work perfectly.


    What I would like to do is to make all of the fields only on the currently active Multipage required but leave the fields on the inactive Multipages as not required. The required fields would need to switch if a new page is activated with a new selection from the combobox.


    Hopefully I'm being clear enough. But please let me know if you need clarification. Thanks, in advance for any help provided!

    Re: Userform: Required Fields on Active Multipage Only


    My apologies RoyUK. A lot of the populated fields is proprietary and/or sensitive so I can't put up the workbook code. Instead, I made a quick sample just to give you an idea. I hope this works.


    On the main portion of the form, there are three fields (All Required Fields). A TextBox for Name, a TextBox for Email, and a ComboBox with three choices.


    Code
    Private Sub UserForm_Initialize()
'Populates Combobox
With ComboBox1
     .AddItem "Page 1"
     .AddItem "Page 2"
     .AddItem "Page 3"
End With
End Sub


    On Page 1 of the MultiPage1, there are three Text Boxes. A TextBox for SSN (Required), a TextBox for Date (Required), and a Multiline TextBox for Details (Not Required).
    [ATTACH=CONFIG]69133[/ATTACH]
    On Page 2 of the MultiPage1, there are three Text Boxes. A TextBox for Height (Required), a TextBox for Weight, and a Multiline TextBox for Details (Required).
    [ATTACH=CONFIG]69134[/ATTACH]
    (I just included Page 3 as blank..but there would be input fields there too with mixes of Required and Not Required)


    The Selection in ComboBox1 determines which Page of the Userform is active with the following code:

    Code
    Private Sub ComboBox1_Change()
'Changes Multipage based on ComboBox1 selection
With Me
        .MultiPage1.Value = .ComboBox1.ListIndex
    End With
End Sub


    The user will ALWAYS only be filling out one page of information.


    When the form is submitted, it sends the information to a table and submits the results via Outlook. In the example, I have filled out all of the information in Page1. However, I can't submit the form because of the Required Fields on Page 2 for 'Height' and 'Details'.


    I would like to make the Userform so that when I select Page 1, only the required fields on that page are required for submission. I want it to treat the fields on Page 2 as non-required, unless I activate Page 2 by selecting that option in Combobox1.


    I hope this clarifies...sorry I couldn't give more code.

    Re: Userform: Required Fields on Active Multipage Only


    There is no code in the form to require any TextBox to be completed. Also, how do you submit, should there be a button?

    Re: Userform: Required Fields on Active Multipage Only


    Hello RoyUK...sorry if I'm making this more complicated. I'm just trying to convey the issue without giving out sensitive information.


    I check for required fields using the following Looped If Else statement:

    Code
    If Me.tbName.Value = "" Then
 Me.tbName.SetFocus
 MsgBox "'Name' is a mandatory field...", vbOKOnly, "Required Field"
 Exit Sub


 ElseIf Me.tbEmail.Value = "" Then
 Me.tbEmail.SetFocus
 MsgBox "'Email' is a mandatory field...", vbOKOnly, "Required Field"



    I then use the

    Code
    Private Sub cmdSubmitEmail_Click()

    to start the Required field check, send the email using


    And send the information to a table in the worksheet:


    Code
    Dim Sheet2 As Worksheet
Set Sheet2 = ThisWorkbook.Sheets("Sheet2")
nr = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheet2.Cells(nr, 1) = tbName
Sheet2.Cells(nr, 2) = tbEmail




    I can add the above loop to all the fields, but then, on the Click Event for the submit button, it's going to tell me that they are empty which, of course, they are, but by design.

    Re: Userform: Required Fields on Active Multipage Only


    I can't see what sensitive information would be given away by having the controls & code on the userform.


    Try this code

    Re: Userform: Required Fields on Active Multipage Only


    Quote from royUK;770850

    I can't see what sensitive information would be given away by having the controls & code on the userform.


    Try this code




    Okay...I guess you're right. Sorry for the scariness, lol. I've stripped out the SSN Lookups, which were the main issues that I had, and I'm attaching the file.


    I added your code into another version but I can't get it to work properly so I've taken it back to where it was prior to the attempt. Please excuse the very basic nature of my coding as this is my first venture into Userforms.



    Thanks for the help thus far...

    Re: Userform: Required Fields on Active Multipage Only


    I'm not sure why you posted all that code. I posted code to check if the textboxes on the active page of the MultiPage have been completed. which is what you asked for. The code replaces your If ... ElseIf code to check the TextBoxes.

    Re: Userform: Required Fields on Active Multipage Only


    Quote from royUK;770872

    I'm not sure why you posted all that code. I posted code to check if the textboxes on the active page of the MultiPage have been completed. which is what you asked for. The code replaces your If ... ElseIf code to check the TextBoxes.


    Thank you SO much. It works like a dream! The problem I had originally had nothing to do with the code to check for the active page of the Multipage...It was including "Page 1, Page 2, etc" in the list for the Combobox because I was already populating that field with a Named Reference (dynamic with Offset for growth) in the following code:


    Code
    For Each cell In [Escalation_Type]
Me.cmbEscalation_Type.AddItem cell
Next cell


    Anywho...it works perfectly! Thanks again for all your help and for putting up with me! :)

    Re: Userform: Required Fields on Active Multipage Only


    Uh oh...one problem.


    Quote

    On Page 1 of the MultiPage1, there are three Text Boxes. A TextBox for SSN (Required), a TextBox for Date (Required), and a Multiline TextBox for Details (Not Required).
    Pg1.JPG
    On Page 2 of the MultiPage1, there are three Text Boxes. A TextBox for Height (Required), a TextBox for Weight, and a Multiline TextBox for Details (Required).
    Pg2.JPG


    In that response, I talked about not all of the fields on each page being Required for submission. Is there any way to specify which are mandatory and which are not? If I had Check boxes, can I make those fields not required? On each Multipage, there are only between 2 and 6 fields that are required.


    Thoughts?

    Re: Userform: Required Fields on Active Multipage Only


    Figured it out! Thanks again for all of your help. What I did was modify your code to include a lookup for the "Tag" property then I included the word "Required" into that property for all the controls I wanted it to check. Instead of only using a Message Box, I also added some coloration to the Required field. Here's the modified code and THANKS AGAIN for all of your help!


    Re: Userform: Required Fields on Active Multipage Only


    Paging royUK!


    Quick question regarding your solution above.


    If I needed to add another Multipage object inside of the previous Multipage object, is there any way that I can make those controls follow the same behavior?


    For example: If the active page of Multipage1 has three textboxes and a combobox that populates Multipage 2. Multipage 2 has three text boxes on page 1 and two textboxes on page 2.


    Can the code you provided be modified so that the three textboxes and the combobox on the active page of Multipage1 are required as well as the three text boxes on the active page of Multipage2...but NOT the two text boxes on the inactive pages (page 2) of Multipage2?

    Re: Userform: Required Fields on Active Multipage Only


    The form is expanding so much that there are constantly a lot of fields that need to be hidden based on choices made in the process. In some of the more simple iterations, I have used conditions to set labels and field visibility but that code gets cumbersome as it's on a per field basis. Here's an example of one:



    If there is a better way, I certainly don't know it. How would you handle a scenario where a combobox choice determines the visibility of multiple fields?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login