Making ALL fields in a userform mandatory

Robert_Conklin

Board Regular
Joined
Jun 19, 2017
Messages
173
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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?
 
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
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top