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?

 

[Robert_Conklin]

Sounds like it would be more trouble than it is worth. I have several combobox's and a list box along with several textboxes.

 

[dmt32]

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?

Hi,
a suggestion I would make is that whilst checking for empty textbox fields in your UserForm is one part of the validation process, your code should also check that the correct data Types have been entered.

For instance, your users entered ABCD in a date field Textbox, code would pass as valid as you are only checking for an empty textbox. Same would apply to Numeric or email address values etc.

You may need to ensure correct formats have been entered by users (& coerced to valid data types where required) before data is submitted.

Something to give consideration to maybe?

Hope Helpful
Dave

 

[Robert_Conklin]

Thanks Dave. That is a great point!

 

[dmt32]

Thanks Dave. That is a great point!

If you want to publish code behind your form, I may be able to adapt some code I provided for another here recently with a similar requirement.

Dave

 

[Robert_Conklin]

I do not have the permissions to attach a photo of my form. Let me get all of my fields together and what would need to happen. I will post again in the AM. Thanks again for everyone's help!

 

[Robert_Conklin]

Ok, below are my fields in the Userform:

T_id = ID (auto-filled textedbox) C_01= Plant Name (combobox) T_02 = Plant # (auto-filled textbox) C_01 = Indicate Action (combobox)
T_04 = SAP # (textbox) T_03 = SAP Vendor # (textbox) T_12 = Purchasing Group (auto-filled textbox) T_13 = Profit Center (auto-filled textbox)
C_05 = Base Unit of Measure (combobox) C_04 = MRP Type (combobox) T_11 = Lot Size (auto-filled textbox) C_03 - Noun (combobox)
T_06 = Modifier (textbox) T_05 = Manufacturer (textbox) T_07 = MFG Part # (textbox) T_09 = Extra Description (textbox)
T_10 = New Part Description (auto-fill textbox) T_08 = SAP Part Description (textbox) T_26 = Struxure Part Description T_14 = Min (textbox)
T_15 = Max T_16 = Bin Location (textbox) C_06 = Material Group (combobox) T_17 = Equipment # or Functional Location (textbox)
C_07 = BOM (combobox) T_23 = Created By (auto-filled textbox) T_24 = Approved By (auto-filled textbox) T_01 = Date Created (auto-filled textbox)
T_25 = Comments (textbox)

In the userform, the end users can make two different types of part maintenance requests and ADD or an EXTEND. Each will have its own required fields.

If "ADD" is selected in C_01 then the following fields will have to be mandatory:

C_02, C01, T_03, C_05, C_04, C_03, T_06, T_05, T_07, T_09, T_14, T_15, T_16, C_06, T_17, C_07, T_25

If "EXTEND" is chosen in field C_01 then the following fields are mandatory:

C_02, C01, T_04, T_03, C_05, C_04, T_08, T_14, T_15, T_16, C_06, T_17, C_07, T_25

Below is my current code for this userform:

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
Private Sub C_02_Click()
'Autofill Plant Number, Profit Center, and Purchasing Group
    T_02.Value = C_02.Column(1)
    T_12.Value = C_02.Column(3)
    T_13.Value = C_02.Column(4)
End Sub
Private Sub C_04_Click()
'Autofill LOT SIZE
    If C_04.Value = "VB" Then T_11.Value = "HB"
    If C_04.Value <> "VB" Then T_11.Value = ""
End Sub
Private Sub CMB_AddNew_Click()
'Code for Submit Button
    T_10.Value = C_03.Value & ";" & T_06.Value & ":" & T_05.Value & "," & T_07.Value & "," & T_09.Value
    Set wsAE = Worksheets("ADD-EXTEND")
    If MsgBox("Correct entry?", vbYesNo + vbQuestion, "Check the data!") = vbNo Then Exit Sub
    iRow = wsAE.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    wsAE.Cells(iRow, 1).Resize(, 30).Value = Array(T_id.Value, C_02.Value, T_02.Value, C_01.Value, T_04.Value, _
        T_03.Value, T_12.Value, T_13.Value, C_05.Value, C_04.Value, T_11.Value, C_03.Value, T_06.Value, _
        T_05.Value, T_07.Value, T_09.Value, T_10.Value, T_08.Value, T_26.Value, T_14.Value, T_15.Value, _
        T_16.Value, C_06.Value, T_17.Value, C_07.Value, T_23.Value, T_24.Value, T_01.Value, "", T_25.Value)
    'Columns.AutoFit
    MsgBox "The new entry has been saved.", vbInformation, "Done"
    For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "ComboBox" Then Ctrl.Value = ""
    Next Ctrl
    LB_01.ListIndex = -1
    LB_01.TopIndex = 0
    Call UserForm_Initialize
End Sub


Private Sub CMB_Change_Click()
'Code for Change Button
    T_01 = Now
    T_23 = Environ("Username")
    T_10.Value = C_03.Value & ";" & T_06.Value & ":" & T_05.Value & "," & T_07.Value & "," & T_09.Value
    Set wsAE = Worksheets("ADD-EXTEND")
    Set rng = wsAE.Range("A2:A" & wsAE.Cells(Rows.Count, "A").End(xlUp).Row)
    Set fnd = rng.Find(What:=T_id.Value, LookIn:=xlValues, Lookat:=xlWhole)
    If LB_01.ListIndex = -1 Then
        MsgBox "First choose a item in the list!", vbCritical, "Attention!"
        Exit Sub
    Else
        If T_id = vbNullString Then
            MsgBox "Customizing is not possible, no entries found", vbExclamation, "Attention!"
            Exit Sub
        ElseIf Not fnd Is Nothing Then
            Application.EnableEvents = False
            If MsgBox("Correct entry?", vbYesNo + vbQuestion, "Check the data!") = vbNo Then Exit Sub
            wsAE.Cells(fnd.Row, "A").Resize(, 30).Value = Array(T_id.Value, C_02.Value, T_02.Value, C_01.Value, _
                T_04.Value, T_03.Value, T_12.Value, T_13.Value, C_05.Value, C_04.Value, T_11.Value, C_03.Value, _
                T_06.Value, T_05.Value, T_07.Value, T_09.Value, T_10.Value, T_08.Value, T_26.Value, T_14.Value, _
                T_15.Value, T_16.Value, C_06.Value, T_17.Value, C_07.Value, T_23.Value, T_24.Value, T_01.Value, _
                "", T_25.Value)
            'Columns.AutoFit
            MsgBox "The changes have been saved.", vbInformation, "Done"
            Application.EnableEvents = True
        End If
        For Each Ctrl In Controls
            If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "ComboBox" Then Ctrl.Value = ""
        Next Ctrl
        LB_01.ListIndex = -1
        LB_01.TopIndex = 0
        Call UserForm_Initialize
    End If
End Sub
Private Sub CMB_Clear_Click()
'Clear all fields
    For Each Ctrl In Controls
        If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "ComboBox" Then Ctrl.Value = ""
    Next Ctrl
    LB_01.ListIndex = -1
    LB_01.TopIndex = 0
    Call UserForm_Initialize
End Sub
Private Sub CMB_Close_Click()
'Close the userform
    Unload Me
End Sub
Private Sub LB_01_Click()
'List box column order
    T_id.Value = LB_01.Column(0)
    C_02.Value = LB_01.Column(1)
    T_02.Value = LB_01.Column(2)
    C_01.Value = LB_01.Column(3)
    T_04.Value = LB_01.Column(4)
    T_03.Value = LB_01.Column(5)
    T_12.Value = LB_01.Column(6)
    T_13.Value = LB_01.Column(7)
    C_05.Value = LB_01.Column(8)
    C_04.Value = LB_01.Column(9)
    T_11.Value = LB_01.Column(10)
    C_03.Value = LB_01.Column(11)
    T_06.Value = LB_01.Column(12)
    T_05.Value = LB_01.Column(13)
    T_07.Value = LB_01.Column(14)
    T_09.Value = LB_01.Column(15)
    T_10.Value = LB_01.Column(16)
    T_08.Value = LB_01.Column(17)
    T_26.Value = LB_01.Column(18)
    T_14.Value = LB_01.Column(19)
    T_15.Value = LB_01.Column(20)
    T_16.Value = LB_01.Column(21)
    C_06.Value = LB_01.Column(22)
    T_17.Value = LB_01.Column(23)
    C_07.Value = LB_01.Column(24)
    T_23.Value = LB_01.Column(25)
    T_24.Value = LB_01.Column(26)
    T_01.Value = LB_01.Column(27)
    T_25.Value = LB_01.Column(29)
End Sub
Private Sub UserForm_Initialize()
'Textbox and Combobox values
    T_id.Value = WorksheetFunction.Max([ids]) + 1
    LB_01.List = [database].Value
    C_01.List = [Action].Value
    C_02.List = [datalist].Value
    C_03.List = [nouns].Value
    C_04.List = [mrp].Value
    C_05.List = [BUoM].Value
    C_06.List = [matgroup].Value
    C_07.List = [bom].Value
    T_01.Value = Now
    T_23.Value = Environ("Username")
End Sub
Private Sub CMB_Approve_Click()
    Dim approveName As String
    T_24.Value = Environ("Username")
    approveName = T_24.Value
    Select Case approveName
    Case "Robert.Conklin", "Bill.Howell", "Tracy.Corbitt", "Danny.Crosby", "Mike.Dees", "Billy.Howell", _
         "Tony.Thompson", "Ron.Lee", "Jerry.Hubbard", "Darrel.Funderburk", "Jason.Moseley", "Radley.Scott", _
         "Vassel.Spencer", "Earl.Howell", "Anthony.Mack", "Jim.West", "Jeremy.Wilt", "Kenneth.Redd", _
         "Graham.Brown", "Domingo.Malave", "Mike.Wester", "Howard.Hendon", "Steve.Barnes", "Troy.Thomas", _
         "Euney.Fontenot", "Paul.Brown", "Darvis.Trahan", "Raul.Garcia", "John.Kocian", "Matt.Doris", "Mike.Kirk", _
         "Richard.Stone", "Steve.King", "Stephan.Grigg", "Robert.Cooper", "Chris.Stewart", "Don.Ziegler", _
         "Jon.Knoop", "Rick.Kocurek", "Brian.Rutecki", "Kenny.Johnson", "Kenyon.Baker", "Gerald.Burt", _
         "Victor.Aldana", "Billy.Reese", "Mike.Nagel", "Andy.Kelly", "Mike.Sulzbach", "Patrick.Dashnaw", _
         "Carl.McMahan", "Rob.Clamp", "Alan.Wilson", "Robert.Faulk", "Armand.Kelle", "Victor.Romero", "Cain.Soto", _
         "Mike.Huston", "Tom.Raggousis", "Keith.Palmer", "Monty.Wood", "Henry.Staley", "Rick.Dubois", _
         "Harold.Wyman", "Steve.Bly", "Tom.Ladd", "Bill.Morrow", "Ron.Porter", "Joseph.Richard", "Angel.Sanchez", _
         "Jaime.Santacruz", "John.Luck", "John.Uzell", "Doug.Meyer", "Ray.Taylor", "Tommy.Garrett", _
         "Chad.Harland", "Scott.Pate", "Paul.LaFond", "Chuck.Harrity", "Tim.DuBose", "Rick.Stolarik", "Dan.Bader", _
         "Johnny.Oliver", "Jeff.Daugherty", "Jerry.Lambert", "Justin.Smith", "David.Seay", "David.Phillips", _
         "Brad.Akers", "Matt.Fountain", "Joe.Hicks", "Wesley.Knapp", "Joey.Bovona", "Colt.Burris", "John.Pacheco", _
         "Frank.Palmer", "Ronnie.Kilgore", "Dana.Taylor", "Tom.Hundley", "Dorman.Karr", "Paul.Staats", _
         "Steve.Johnson", "Ricky.Dixon", "Les.Parrish", "Terry.Simmons", "Chad.Metevier", "Ciro.Garcia", _
         "Steve.Nichols", "Danny.Bennett", "Randy.Johnson"
         If MsgBox("Hey " & T_24.Value & " are you sure you want to approve all?", vbYesNo + vbQuestion, "Sure!") = vbNo Then Exit Sub
        Set rng = [ids]
        For Each cell In rng
            If cell.Value <> "" Then
                cell.Offset(0, 26).Value = T_24.Value
                cell.Offset(0, 28).Value = Now
            End If
        Next
        MsgBox "Approved!", vbInformation, "Done."
        Case Else
        MsgBox "You Do Not Have Authorization To Approve This!", vbCritical, "Attention"
        T_24.Value = ""
    End Select
End Sub

 

[dmt32]

well that's a challenge, will have to find quiet moment when grandchildren not here to work through your code.

As an aside, suggest don't hard code all your approver's rather, create a list in worksheet (easier to maintain) and read from that.

Place following in STANDARD module

Option Base 1
Sub ApprovedUsers()
Dim ws As Worksheet
Dim arr

arr = Array("Robert.Conklin", "Bill.Howell", "Tracy.Corbitt", "Danny.Crosby", "Mike.Dees", "Billy.Howell", _
         "Tony.Thompson", "Ron.Lee", "Jerry.Hubbard", "Darrel.Funderburk", "Jason.Moseley", "Radley.Scott", _
         "Vassel.Spencer", "Earl.Howell", "Anthony.Mack", "Jim.West", "Jeremy.Wilt", "Kenneth.Redd", _
         "Graham.Brown", "Domingo.Malave", "Mike.Wester", "Howard.Hendon", "Steve.Barnes", "Troy.Thomas", _
         "Euney.Fontenot", "Paul.Brown", "Darvis.Trahan", "Raul.Garcia", "John.Kocian", "Matt.Doris", "Mike.Kirk", _
         "Richard.Stone", "Steve.King", "Stephan.Grigg", "Robert.Cooper", "Chris.Stewart", "Don.Ziegler", _
         "Jon.Knoop", "Rick.Kocurek", "Brian.Rutecki", "Kenny.Johnson", "Kenyon.Baker", "Gerald.Burt", _
         "Victor.Aldana", "Billy.Reese", "Mike.Nagel", "Andy.Kelly", "Mike.Sulzbach", "Patrick.Dashnaw", _
         "Carl.McMahan", "Rob.Clamp", "Alan.Wilson", "Robert.Faulk", "Armand.Kelle", "Victor.Romero", "Cain.Soto", _
         "Mike.Huston", "Tom.Raggousis", "Keith.Palmer", "Monty.Wood", "Henry.Staley", "Rick.Dubois", _
         "Harold.Wyman", "Steve.Bly", "Tom.Ladd", "Bill.Morrow", "Ron.Porter", "Joseph.Richard", "Angel.Sanchez", _
         "Jaime.Santacruz", "John.Luck", "John.Uzell", "Doug.Meyer", "Ray.Taylor", "Tommy.Garrett", _
         "Chad.Harland", "Scott.Pate", "Paul.LaFond", "Chuck.Harrity", "Tim.DuBose", "Rick.Stolarik", "Dan.Bader", _
         "Johnny.Oliver", "Jeff.Daugherty", "Jerry.Lambert", "Justin.Smith", "David.Seay", "David.Phillips", _
         "Brad.Akers", "Matt.Fountain", "Joe.Hicks", "Wesley.Knapp", "Joey.Bovona", "Colt.Burris", "John.Pacheco", _
         "Frank.Palmer", "Ronnie.Kilgore", "Dana.Taylor", "Tom.Hundley", "Dorman.Karr", "Paul.Staats", _
         "Steve.Johnson", "Ricky.Dixon", "Les.Parrish", "Terry.Simmons", "Chad.Metevier", "Ciro.Garcia", _
         "Steve.Nichols", "Danny.Bennett", "Randy.Johnson")
         
 Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
 ws.Name = "Approvers"
 
 ws.Cells(1, 1).Resize(UBound(arr, 1)).Value = Application.transpose(arr)
 ws.Columns(1).AutoFit
End Sub

Run it then delete it.
This should create Approvers worksheet (which can hide)

Try this Update to CMB_Approve_Click

Private Sub CMB_Approve_Click()
    Dim approveName As String
    Dim m As Variant
    Dim ApproverList As Range
    
    Set T_24 = Cells(2, 2)
    
    T_24.Value = Environ("Username")
    approveName = T_24.Value
    
    With Worksheets("Approvers")
        Set ApproverList = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp))
    End With
    
    m = Application.Match(approveName, ApproverList, False)

    If Not IsError(m) Then
        If MsgBox("Hey " & T_24.Value & " are you sure you want to approve all?", vbYesNo + vbQuestion, "Sure!") = vbNo Then Exit Sub
        Set rng = [ids]
        For Each cell In rng
            If cell.Value <> "" Then
                cell.Offset(0, 26).Value = T_24.Value
                cell.Offset(0, 28).Value = Now
            End If
        Next
        MsgBox "Approved!", vbInformation, "Done."
    Else
        MsgBox approveName & Chr(10) & "You Do Not Have Authorization To Approve This!", vbCritical, "Attention"
        T_24.Value = ""
    End If
End Sub

Will have to come back to you on main issue later

Hope Helpful

Dave

 

[dmt32]

Edit

sorry, forgot to delete this line I inserted when testing:

Set T_24 = Cells(2, 2)

Dave

 

[Robert_Conklin]

I already had a list of authorized users on another worksheet. I replaced "Approvers" with the worksheet I already had and set the range. It works perfectly. Thank you.

 

[dmt32]

I already had a list of authorized users on another worksheet. I replaced "Approvers" with the worksheet I already had and set the range. It works perfectly. Thank you.

Most welcome -
If I could encourage you to press appropriate rating button on the new system - Us contributors here have all had our ratings set back to zero!