Making ALL fields in a userform mandatory - Page 2

Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 22

Thread: Making ALL fields in a userform mandatory

  1. #11
    New Member
    Join Date
    Jun 2017
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

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

  2. #12
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    3,887
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

    Quote Originally Posted by Robert_Conklin View Post
    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

  3. #13
    New Member
    Join Date
    Jun 2017
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

    Thanks Dave. That is a great point!

  4. #14
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    3,887
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

    Quote Originally Posted by Robert_Conklin View Post
    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
    Last edited by dmt32; Aug 1st, 2017 at 04:17 PM.

  5. #15
    New Member
    Join Date
    Jun 2017
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

    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!

  6. #16
    New Member
    Join Date
    Jun 2017
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

    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:

    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
    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

  7. #17
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    3,887
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

    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

    Code:
    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

    Code:
    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
    Last edited by dmt32; Aug 2nd, 2017 at 11:12 AM.

  8. #18
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    3,887
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

    Edit

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

    Code:
    Set T_24 = Cells(2, 2)
    Dave

  9. #19
    New Member
    Join Date
    Jun 2017
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

    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.

  10. #20
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    3,887
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Making ALL fields in a userform mandatory

      
    Quote Originally Posted by Robert_Conklin View Post
    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!

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
  •  

 

 
DMCA.com