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
