Making ALL fields in a userform mandatory - Page 3

Page 3 of 3 FirstFirst 123
Results 21 to 22 of 22

Thread: Making ALL fields in a userform mandatory

  1. #21
    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

     
    Final Code: Works like a champ!! Thank you VERY much for everything!!

    Code:
    Option Base 1Function IsComplete(ByVal Form As Object, ByRef ControlValue As Variant, ByVal Action As Integer) As Boolean
        Dim msg As String, Ctrl() As String, UsersName As String
        Dim m As Variant
        Dim i As Integer
        Dim ValidEntry As Boolean, EntryRequired As Boolean
        
    'highlight controls if not valid entry
        Const HighlightEntryErrors As Boolean = True
        
        UsersName = Form.T_23.Text
        
        For i = 1 To UBound(ControlValue)
            Ctrl = Split(ControlValue(i), ",")
            If Ctrl(1) <> "Blank" Then
                With Form.Controls(Ctrl(0))
    'confirm data entered
                    ValidEntry = CBool(Len(.Value) > 0)
    'required controls
                    m = Application.Match(.Name, RequiredControls(Action), False)
    'if match, data entry required
                    EntryRequired = Not IsError(m)
                    If EntryRequired And Not ValidEntry Then msg = msg & Ctrl(1) & Chr(10) Else ValidEntry = True
                    
    'validate data type entered
                    Select Case .Name
    'Numeric Fields
                    Case "T_14", "T_15"
                        ValidEntry = CBool(IsNumeric(.Value))
                        If Not ValidEntry Then
                            If Len(.Text) > 0 Then msg = msg & Ctrl(1) & " (Numeric Values Only)" & Chr(10)
                        Else
    'enter value to array
                            ControlValue(i) = .Value
                        End If
    'date fields
                    Case "T_01"
                        ValidEntry = CBool(IsDate(.Value))
                        If ValidEntry Then
    'corece date string
                            ControlValue(i) = DateValue(.Value)
                        Else
    'invalid date
                            If Len(.Text) > 0 Then msg = msg & Ctrl(1) & " (Invalid Date Entry)" & Chr(10)
                        End If
    
    
    
    
                    Case Else
                        
    'enter all other values to array
                        ControlValue(i) = .Value
                    End Select
    'highlight invalid fields
                If HighlightEntryErrors Then .BackColor = IIf(ValidEntry, vbWhite, vbRed)
                End With
            Else
    'blank field
                ControlValue(i) = ""
            End If
            Next i
    'inform user
            If Len(msg) > 0 Then
    'entry error(s)
                MsgBox UsersName & Chr(10) & "The Following Fields Must Be Completed:" & Chr(10) & Chr(10) & msg, 48, "Required Entry"
            Else
    'all ok
                IsComplete = True
            End If
    End Function
    Function RequiredControls(ByVal Action As Integer) As Variant
    
    
    
    
    'define mandatory fields for each Action
    If Action = xlAdd Then
    '"ADD" is Chosen in C_01
        RequiredControls = Array("C_02", "C_03", "C_04", "C_05", "C_06", "C_07", _
                                 "T_03", "T_06", "T_05", "T_07", "T_09", "T_11", "T_14", _
                                 "T_15", "T_16", "T_17", "T_25")
    Else
    '"EXTEND" is Chosen in C_01
       RequiredControls = Array("C_02", "C_04", "C_05", "C_06", "C_07", _
                                 "T_04", "T_03", "T_08", "T_11", "T_14", "T_15", _
                                 "T_16", "T_17", "T_25")
    End If
    End Function
    Function DataEntry() As Variant
    'function lists the data entry control names
    'and associated error messages for required fields
    
    
    
    
    '1st part of each array element is the control
    '2nd part is the controls error prompt
    
    
    
    
        DataEntry = Array("T_id,ID", "C_02,Plant Name", _
                          "T_02,Plant #", "C_01,Indicate Action", _
                          "T_04,SAP #", "T_03,SAP Vendor #", _
                          "T_12,Purchasing Group", "T_13,Profit Center", _
                          "C_05,Base Unit of Measure", "C_04,MRP Type", _
                          "T_11,Lot Size", "C_03,Noun", _
                          "T_06,Modifier", "T_05,Manufacturer", _
                          "T_07,MFG Part #", "T_09,Extra Description", _
                          "T_10,New Part Description", "T_08,SAP Part Description", _
                          "T_26,Struxure Part Description", "T_14,Min", _
                          "T_15,Max", "T_16,Bin Location", _
                          "C_06,Material Group", "T_17,Equipment # or Functional Location", _
                          "C_07,BOM", "T_23,Created By", _
                          "T_24,Approved By", "T_01,Date Created", _
                          """,Blank", "T_25,Comments")
    End Function

  2. #22
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    3,883
    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
    Final Code: Works like a champ!! Thank you VERY much for everything!!
    Most welcome - many thanks for feedback - very much appreciated.

    Dave

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