Building a VBA-Powered Data Entry Validation Engine: Real-Time Input Checks, Dependent Drop-Downs, and Audit Logging in a UserForm

Introduction

Picture this: you've handed off a workbook to a team of ten people. By Monday morning, your "Customer Region" column contains "EMEA," "emea," "Europe/Middle East," "EM EA," and three blank cells. Your "Contract Value" column has dollar signs in some cells, commas in others, and one entry that just says "big." Your audit deadline is Wednesday.

This is the data quality problem that kills real analytical work. Native Excel data validation helps at the margins, but it's fundamentally passive — it blocks bad input after someone types it, offers no contextual guidance, doesn't adapt to what users have already entered, and produces no record of who changed what and when. For serious, multi-user data entry workbooks, you need something built to a higher standard.

By the end of this lesson, you'll have built a complete VBA validation engine hosted in a UserForm that catches bad input in real time as users type, serves dependent drop-downs that change their options based on upstream selections, prevents form submission when validation rules aren't met, and writes a detailed audit log entry for every successful record. This isn't a toy — it's a production-grade pattern you can adapt for any data entry scenario.

What you'll learn:

• How to architect a multi-rule validation engine inside a UserForm using event-driven VBA
• How to implement real-time input checks using Change and Exit events on individual controls
• How to wire up dependent (cascading) drop-downs where child options filter based on parent selection
• How to build a centralized validation dispatcher that aggregates rule results and controls the Submit button state
• How to write a timestamped, user-identified audit log to a protected worksheet every time a valid record is committed

---

Prerequisites

You should be comfortable writing VBA procedures, working with the UserForm designer, and navigating Excel's object model. If you're newer to UserForms, Building UserForms for Custom Data Entry Interfaces covers the fundamentals. You should also understand VBA event handling — if Building a Custom VBA Event-Driven Framework isn't familiar territory, skim it before proceeding. A working understanding of VBA Variables, Data Types, and Control Structures is assumed throughout.

---

The Scenario: A Sales Order Entry Form

We'll build around a concrete, realistic scenario: a sales team entering new orders into a workbook. Each order has:

• Region (drop-down: EMEA, APAC, AMER)
• Country (dependent drop-down: filters to countries within the selected region)
• Product Category (drop-down: Hardware, Software, Services)
• Product SKU (dependent drop-down: filters to SKUs within the selected category)
• Contract Value (numeric, $1,000 minimum, $10,000,000 maximum)
• Customer PO Number (text, must match pattern PO- followed by 6–10 digits)
• Deal Close Date (date, must be in the future, not a weekend)
• Sales Rep Email (must be a valid email format from your company domain)

This gives us enough variety to demonstrate every validation pattern you'll encounter in the real world: format validation, range validation, pattern matching, date logic, and cascading dependencies.

---

Workbook Architecture: Setting Up the Data Foundation

Before writing a line of form code, you need to think about where your reference data lives and how your form will reach it. Hardcoding lists inside VBA is a maintenance disaster — when marketing adds a new SKU, nobody wants to open the VBA editor.

Create three worksheets:

RefData (hidden): Contains all your reference lists in named tables. Set up the following ranges:

• tblRegions: A1:A3 → EMEA, APAC, AMER
• tblCountries: Columns A–C, header row with region names, countries listed below each
• tblCategories: A1:A3 → Hardware, Software, Services
• tblSKUs: Columns A–C, header row with category names, SKUs listed below each

Orders (visible): Your destination table — a structured Excel table named tblOrders with columns matching your form fields plus SubmittedBy and SubmittedAt.

AuditLog (very hidden): We'll set this to xlSheetVeryHidden via VBA so casual users can't unhide it through the UI.

Set up the AuditLog table with these columns: Timestamp, Username, Action, Field, OldValue, NewValue, RecordID, FormSessionID.

Now add a standard module called modConstants to hold your configuration:

' modConstants.bas
Option Explicit

Public Const REFDATA_SHEET As String = "RefData"
Public Const ORDERS_SHEET As String = "Orders"
Public Const AUDITLOG_SHEET As String = "AuditLog"
Public Const MIN_CONTRACT_VALUE As Double = 1000
Public Const MAX_CONTRACT_VALUE As Double = 10000000
Public Const COMPANY_DOMAIN As String = "@acmecorp.com"
Public Const PO_PATTERN As String = "PO-"
Public Const PO_MIN_DIGITS As Integer = 6
Public Const PO_MAX_DIGITS As Integer = 10

Using named constants instead of magic numbers throughout your form code is not optional at this level — it's what separates maintainable automation from archaeology.

---

Designing the UserForm Layout

In the VBA editor, insert a new UserForm and name it frmOrderEntry. Set its Caption to "New Sales Order" and size it to approximately 480 × 560 pixels.

Add the following controls with these exact names (naming is critical — our event handlers depend on it):

Control Type	Name	Label Text
ComboBox	cboRegion	Region
ComboBox	cboCountry	Country
ComboBox	cboCategory	Product Category
ComboBox	cboSKU	Product SKU
TextBox	txtContractValue	Contract Value ($)
TextBox	txtPONumber	Customer PO Number
TextBox	txtCloseDate	Deal Close Date (MM/DD/YYYY)
TextBox	txtEmail	Sales Rep Email
Label	lblContractError	(empty, red text)
Label	lblPOError	(empty, red text)
Label	lblDateError	(empty, red text)
Label	lblEmailError	(empty, red text)
CommandButton	btnSubmit	Submit Order
CommandButton	btnCancel	Cancel

Place each error label directly below its corresponding input control. Set all error labels' ForeColor to red (&H000000FF& for pure red in VBA's BGR notation — actually &H000000FF is blue; use RGB(200, 0, 0) set in code instead). Set btnSubmit.Enabled = False as the initial state.

For the validation status indicators, you can add small colored shape labels or simply use the error label text approach — showing an inline message below each field is more accessible and more informative than icon-only feedback.

---

Building the Validation Engine Architecture

Here's where most tutorials go wrong: they put validation logic directly inside each control's event handler. That leads to duplicated logic, inconsistent behavior, and a form where the Submit button might enable even when some fields are invalid because you forgot to check them all.

The right pattern is a centralized validation dispatcher. Here's how it works:

1. Each control's event handler calls ValidateField(fieldName) when its content changes
2. ValidateField runs the specific rule(s) for that field and updates a module-level dictionary of field → isValid status
3. ValidateField updates the error label for that specific field
4. After updating the dictionary, it calls UpdateSubmitButton
5. UpdateSubmitButton checks whether ALL fields are valid and enables/disables btnSubmit accordingly

This means there's exactly one place where the submit button state is computed, and it always reflects the true state of all fields simultaneously.

Start by adding a module-level validation state tracker to the form's code module:

' frmOrderEntry code module
Option Explicit

' Validation state dictionary — True = valid, False = invalid/empty
Private mValidFields As Object  ' Scripting.Dictionary

' Unique session ID for audit logging
Private mSessionID As String

' Previous values for audit trail (stored on field exit)
Private mPrevContractValue As String
Private mPrevPONumber As String
Private mPrevCloseDate As String
Private mPrevEmail As String

Initialize these in UserForm_Initialize:

Private Sub UserForm_Initialize()
    ' Create validation state tracker
    Set mValidFields = CreateObject("Scripting.Dictionary")
    
    ' Initialize all required fields as invalid (empty = invalid)
    mValidFields("Region") = False
    mValidFields("Country") = False
    mValidFields("Category") = False
    mValidFields("SKU") = False
    mValidFields("ContractValue") = False
    mValidFields("PONumber") = False
    mValidFields("CloseDate") = False
    mValidFields("Email") = False
    
    ' Generate a unique session ID for this form open event
    mSessionID = Format(Now, "YYYYMMDDHHMMSS") & "_" & Environ("USERNAME")
    
    ' Load the independent drop-downs
    LoadRegions
    LoadCategories
    
    ' Disable submit until everything is valid
    btnSubmit.Enabled = False
    
    ' Clear all error labels
    ClearAllErrors
End Sub

Now write the two architectural cornerstones — UpdateSubmitButton and a generic SetFieldValid helper:

Private Sub UpdateSubmitButton()
    Dim allValid As Boolean
    Dim key As Variant
    
    allValid = True
    For Each key In mValidFields.Keys
        If Not mValidFields(key) Then
            allValid = False
            Exit For
        End If
    Next key
    
    btnSubmit.Enabled = allValid
End Sub

Private Sub SetFieldValid(fieldKey As String, isValid As Boolean, _
                          errorLabel As MSForms.Label, errorMsg As String)
    mValidFields(fieldKey) = isValid
    
    If isValid Then
        errorLabel.Caption = ""
    Else
        errorLabel.Caption = errorMsg
    End If
    
    UpdateSubmitButton
End Sub

Private Sub ClearAllErrors()
    lblContractError.Caption = ""
    lblPOError.Caption = ""
    lblDateError.Caption = ""
    lblEmailError.Caption = ""
End Sub

This is clean architecture. SetFieldValid handles three things in one call: updating the dictionary, updating the error UI, and triggering the submit button recompute. Every validation rule will call this function rather than manipulating state directly.

---

Loading Reference Data and the Dependent Drop-Down Engine

The LoadRegions and LoadCategories functions pull from your RefData sheet:

Private Sub LoadRegions()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    
    Set ws = ThisWorkbook.Worksheets(REFDATA_SHEET)
    Set rng = ws.ListObjects("tblRegions").DataBodyRange
    
    cboRegion.Clear
    cboRegion.AddItem ""  ' Blank placeholder
    
    For Each cell In rng
        If cell.Value <> "" Then
            cboRegion.AddItem cell.Value
        End If
    Next cell
End Sub

Private Sub LoadCategories()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    
    Set ws = ThisWorkbook.Worksheets(REFDATA_SHEET)
    Set rng = ws.ListObjects("tblCategories").DataBodyRange
    
    cboCategory.Clear
    cboCategory.AddItem ""
    
    For Each cell In rng
        If cell.Value <> "" Then
            cboCategory.AddItem cell.Value
        End If
    Next cell
End Sub

The real work happens in the dependent loaders. When a user selects a region, we need to populate cboCountry with only the countries for that region. Our RefData sheet has region names as column headers in the Countries table — so we find the matching column header and pull the values beneath it:

Private Sub LoadCountriesForRegion(regionName As String)
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim headerRow As Range
    Dim col As Range
    Dim cell As Range
    Dim colIndex As Long
    
    cboCountry.Clear
    cboCountry.AddItem ""
    
    ' Reset country validity when parent changes
    mValidFields("Country") = False
    
    If regionName = "" Then
        cboCountry.Enabled = False
        Exit Sub
    End If
    
    Set ws = ThisWorkbook.Worksheets(REFDATA_SHEET)
    Set tbl = ws.ListObjects("tblCountries")
    
    ' Find the column matching the selected region
    colIndex = 0
    For Each col In tbl.HeaderRowRange
        If col.Value = regionName Then
            colIndex = col.Column - tbl.HeaderRowRange.Column + 1
            Exit For
        End If
    Next col
    
    If colIndex = 0 Then
        MsgBox "Region '" & regionName & "' not found in reference data.", vbExclamation
        Exit Sub
    End If
    
    ' Load countries from that column's data body
    For Each cell In tbl.ListColumns(colIndex).DataBodyRange
        If cell.Value <> "" Then
            cboCountry.AddItem cell.Value
        End If
    Next cell
    
    cboCountry.Enabled = True
End Sub

Notice that LoadCountriesForRegion also resets mValidFields("Country") to False. This is crucial: if a user picks "EMEA" → selects "France" → then changes to "APAC," the country selection is now invalid (France isn't in APAC). The dependent field must be invalidated when its parent changes.

Apply the same pattern for SKUs based on category:

Private Sub LoadSKUsForCategory(categoryName As String)
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim col As Range
    Dim cell As Range
    Dim colIndex As Long
    
    cboSKU.Clear
    cboSKU.AddItem ""
    mValidFields("SKU") = False
    
    If categoryName = "" Then
        cboSKU.Enabled = False
        Exit Sub
    End If
    
    Set ws = ThisWorkbook.Worksheets(REFDATA_SHEET)
    Set tbl = ws.ListObjects("tblSKUs")
    
    colIndex = 0
    For Each col In tbl.HeaderRowRange
        If col.Value = categoryName Then
            colIndex = col.Column - tbl.HeaderRowRange.Column + 1
            Exit For
        End If
    Next col
    
    If colIndex = 0 Then Exit Sub
    
    For Each cell In tbl.ListColumns(colIndex).DataBodyRange
        If cell.Value <> "" Then cboSKU.AddItem cell.Value
    Next cell
    
    cboSKU.Enabled = True
End Sub

Now wire these loaders to the Change events on the parent drop-downs:

Private Sub cboRegion_Change()
    LoadCountriesForRegion cboRegion.Value
    
    ' Validate the region selection itself
    SetFieldValid "Region", (cboRegion.Value <> ""), _
                  lblContractError, ""  ' Regions don't have a dedicated error label
    
    ' Force revalidation of child
    UpdateSubmitButton
End Sub

Private Sub cboCountry_Change()
    SetFieldValid "Country", (cboCountry.Value <> ""), _
                  lblContractError, ""
    UpdateSubmitButton
End Sub

Private Sub cboCategory_Change()
    LoadSKUsForCategory cboCategory.Value
    SetFieldValid "Category", (cboCategory.Value <> ""), _
                  lblContractError, ""
    UpdateSubmitButton
End Sub

Private Sub cboSKU_Change()
    SetFieldValid "SKU", (cboSKU.Value <> ""), _
                  lblContractError, ""
    UpdateSubmitButton
End Sub

---

Real-Time Validation Rules

Now we write the actual rules. Each one validates a specific field and calls SetFieldValid with an appropriate error message.

Contract Value Validation

Private Sub txtContractValue_Change()
    ValidateContractValue
End Sub

Private Sub txtContractValue_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    ' Store old value for audit trail before user navigates away
    mPrevContractValue = txtContractValue.Value
    ValidateContractValue
End Sub

Private Sub ValidateContractValue()
    Dim rawValue As String
    Dim cleanValue As String
    Dim numValue As Double
    Dim isValid As Boolean
    Dim errorMsg As String
    
    rawValue = Trim(txtContractValue.Value)
    
    ' Strip common formatting characters users might type
    cleanValue = Replace(rawValue, "$", "")
    cleanValue = Replace(cleanValue, ",", "")
    cleanValue = Trim(cleanValue)
    
    isValid = False
    errorMsg = ""
    
    If cleanValue = "" Then
        errorMsg = "Contract value is required."
    ElseIf Not IsNumeric(cleanValue) Then
        errorMsg = "Enter a numeric value (e.g., 50000)."
    Else
        numValue = CDbl(cleanValue)
        If numValue < MIN_CONTRACT_VALUE Then
            errorMsg = "Minimum contract value is $" & Format(MIN_CONTRACT_VALUE, "#,##0")
        ElseIf numValue > MAX_CONTRACT_VALUE Then
            errorMsg = "Maximum contract value is $" & Format(MAX_CONTRACT_VALUE, "#,##0")
        Else
            isValid = True
        End If
    End If
    
    SetFieldValid "ContractValue", isValid, lblContractError, errorMsg
End Sub

Notice that we strip $ and , before validating. Users will type them. Fighting that natural behavior is bad UX — strip the noise and validate the substance.

PO Number Pattern Validation

This one uses a regular expression, which gives us precise pattern matching without building our own character-by-character parser:

Private Sub txtPONumber_Change()
    ValidatePONumber
End Sub

Private Sub txtPONumber_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    mPrevPONumber = txtPONumber.Value
    ValidatePONumber
End Sub

Private Sub ValidatePONumber()
    Dim rawValue As String
    Dim isValid As Boolean
    Dim errorMsg As String
    Dim regex As Object
    
    rawValue = Trim(txtPONumber.Value)
    isValid = False
    errorMsg = ""
    
    If rawValue = "" Then
        errorMsg = "PO Number is required."
    Else
        Set regex = CreateObject("VBScript.RegExp")
        regex.Pattern = "^PO-\d{6,10}$"
        regex.IgnoreCase = False
        
        If regex.Test(rawValue) Then
            isValid = True
        Else
            errorMsg = "Format must be PO- followed by 6 to 10 digits (e.g., PO-123456)."
        End If
        
        Set regex = Nothing
    End If
    
    SetFieldValid "PONumber", isValid, lblPOError, errorMsg
End Sub

Date Validation

Private Sub txtCloseDate_Change()
    ValidateCloseDate
End Sub

Private Sub txtCloseDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    mPrevCloseDate = txtCloseDate.Value
    ValidateCloseDate
End Sub

Private Sub ValidateCloseDate()
    Dim rawValue As String
    Dim parsedDate As Date
    Dim isValid As Boolean
    Dim errorMsg As String
    Dim dayOfWeek As Integer
    
    rawValue = Trim(txtCloseDate.Value)
    isValid = False
    errorMsg = ""
    
    If rawValue = "" Then
        errorMsg = "Close date is required."
    ElseIf Not IsDate(rawValue) Then
        errorMsg = "Enter a valid date in MM/DD/YYYY format."
    Else
        parsedDate = CDate(rawValue)
        
        If parsedDate <= Date Then
            errorMsg = "Close date must be in the future."
        Else
            dayOfWeek = Weekday(parsedDate, vbMonday)  ' Monday = 1, Sunday = 7
            If dayOfWeek >= 6 Then  ' Saturday = 6, Sunday = 7
                errorMsg = "Close date cannot fall on a weekend."
            Else
                isValid = True
            End If
        End If
    End If
    
    SetFieldValid "CloseDate", isValid, lblDateError, errorMsg
End Sub

Email Validation

Private Sub txtEmail_Change()
    ValidateEmail
End Sub

Private Sub txtEmail_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    mPrevEmail = txtEmail.Value
    ValidateEmail
End Sub

Private Sub ValidateEmail()
    Dim rawValue As String
    Dim isValid As Boolean
    Dim errorMsg As String
    Dim regex As Object
    
    rawValue = Trim(LCase(txtEmail.Value))
    isValid = False
    errorMsg = ""
    
    If rawValue = "" Then
        errorMsg = "Sales rep email is required."
    Else
        Set regex = CreateObject("VBScript.RegExp")
        ' Reasonably thorough email pattern
        regex.Pattern = "^[a-z0-9._%+\-]+@[a-z0-9.\-]+\.[a-z]{2,}$"
        regex.IgnoreCase = True
        
        If Not regex.Test(rawValue) Then
            errorMsg = "Enter a valid email address."
        ElseIf Right(rawValue, Len(COMPANY_DOMAIN)) <> LCase(COMPANY_DOMAIN) Then
            errorMsg = "Email must be an " & COMPANY_DOMAIN & " address."
        Else
            isValid = True
        End If
        
        Set regex = Nothing
    End If
    
    SetFieldValid "Email", isValid, lblEmailError, errorMsg
End Sub

---

The Audit Logging Engine

The audit log is the feature that transforms this from a convenience tool into an enterprise-grade system. Every form submission gets logged. But we'll go further — we also log when users change their mind mid-entry on key fields, capturing the old and new values.

First, set up the AuditLog sheet protection in your workbook open event (add this to ThisWorkbook):

Private Sub Workbook_Open()
    ' Ensure audit log sheet stays very hidden
    Dim wsAudit As Worksheet
    Set wsAudit = ThisWorkbook.Worksheets(AUDITLOG_SHEET)
    wsAudit.Visible = xlSheetVeryHidden
End Sub

Now the core logging function in a standard module called modAuditLog:

' modAuditLog.bas
Option Explicit

Public Sub WriteAuditEntry(action As String, fieldName As String, _
                            oldValue As String, newValue As String, _
                            recordID As String, sessionID As String)
    Dim ws As Worksheet
    Dim nextRow As Long
    Dim userName As String
    
    On Error GoTo AuditError
    
    Set ws = ThisWorkbook.Worksheets(AUDITLOG_SHEET)
    
    ' Temporarily allow writes to the very-hidden sheet
    ' (Very-hidden doesn't protect from VBA writes, only from UI)
    
    nextRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    
    ' If this is the first entry, write headers
    If nextRow = 2 And ws.Cells(1, 1).Value = "" Then
        ws.Cells(1, 1).Value = "Timestamp"
        ws.Cells(1, 2).Value = "Username"
        ws.Cells(1, 3).Value = "Action"
        ws.Cells(1, 4).Value = "Field"
        ws.Cells(1, 5).Value = "OldValue"
        ws.Cells(1, 6).Value = "NewValue"
        ws.Cells(1, 7).Value = "RecordID"
        ws.Cells(1, 8).Value = "SessionID"
    End If
    
    userName = Environ("USERNAME")
    
    ws.Cells(nextRow, 1).Value = Now
    ws.Cells(nextRow, 1).NumberFormat = "YYYY-MM-DD HH:MM:SS"
    ws.Cells(nextRow, 2).Value = userName
    ws.Cells(nextRow, 3).Value = action
    ws.Cells(nextRow, 4).Value = fieldName
    ws.Cells(nextRow, 5).Value = oldValue
    ws.Cells(nextRow, 6).Value = newValue
    ws.Cells(nextRow, 7).Value = recordID
    ws.Cells(nextRow, 8).Value = sessionID
    
    Exit Sub
    
AuditError:
    ' Audit failures should never crash the application
    ' Log to immediate window but continue
    Debug.Print "AUDIT FAILURE: " & Err.Description & " at " & Now
    Resume Next
End Sub

Now wire the audit log into the form's submission handler and into mid-entry change detection. The mid-entry change capture fires in the _Exit events, comparing the old stored value to the new one:

' In frmOrderEntry — add this to txtContractValue_Exit
Private Sub txtContractValue_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim newValue As String
    newValue = Trim(txtContractValue.Value)
    
    ' Log the field change if value actually changed
    If mPrevContractValue <> "" And mPrevContractValue <> newValue Then
        WriteAuditEntry "FIELD_EDIT", "ContractValue", _
                        mPrevContractValue, newValue, "", mSessionID
    End If
    
    mPrevContractValue = newValue
    ValidateContractValue
End Sub

Apply the same pattern to txtPONumber_Exit, txtCloseDate_Exit, and txtEmail_Exit, substituting the appropriate field name and previous-value variable.

---

The Submit Handler: Committing Data and Writing the Final Audit Entry

Private Sub btnSubmit_Click()
    Dim wsOrders As Worksheet
    Dim tblOrders As ListObject
    Dim newRow As ListRow
    Dim recordID As String
    Dim contractValue As Double
    
    ' Final validation pass — belt and suspenders
    If Not AllFieldsValid() Then
        MsgBox "Please correct validation errors before submitting.", vbExclamation
        Exit Sub
    End If
    
    On Error GoTo SubmitError
    
    Set wsOrders = ThisWorkbook.Worksheets(ORDERS_SHEET)
    Set tblOrders = wsOrders.ListObjects("tblOrders")
    
    ' Add a new row to the structured table
    Set newRow = tblOrders.ListRows.Add
    
    ' Generate a record ID
    recordID = "ORD-" & Format(Now, "YYYYMMDDHHMMSS") & "-" & _
               Format(tblOrders.ListRows.Count, "0000")
    
    ' Clean and parse the contract value
    contractValue = CDbl(Replace(Replace(txtContractValue.Value, "$", ""), ",", ""))
    
    ' Write to table columns by header name — robust against column reordering
    newRow.Range(1, GetColumnIndex(tblOrders, "RecordID")).Value = recordID
    newRow.Range(1, GetColumnIndex(tblOrders, "Region")).Value = cboRegion.Value
    newRow.Range(1, GetColumnIndex(tblOrders, "Country")).Value = cboCountry.Value
    newRow.Range(1, GetColumnIndex(tblOrders, "Category")).Value = cboCategory.Value
    newRow.Range(1, GetColumnIndex(tblOrders, "SKU")).Value = cboSKU.Value
    newRow.Range(1, GetColumnIndex(tblOrders, "ContractValue")).Value = contractValue
    newRow.Range(1, GetColumnIndex(tblOrders, "PONumber")).Value = txtPONumber.Value
    newRow.Range(1, GetColumnIndex(tblOrders, "CloseDate")).Value = CDate(txtCloseDate.Value)
    newRow.Range(1, GetColumnIndex(tblOrders, "Email")).Value = LCase(Trim(txtEmail.Value))
    newRow.Range(1, GetColumnIndex(tblOrders, "SubmittedBy")).Value = Environ("USERNAME")
    newRow.Range(1, GetColumnIndex(tblOrders, "SubmittedAt")).Value = Now
    
    ' Write the submission audit entry
    WriteAuditEntry "SUBMIT", "AllFields", "", _
                    "Region=" & cboRegion.Value & "|Country=" & cboCountry.Value & _
                    "|SKU=" & cboSKU.Value & "|Value=" & contractValue, _
                    recordID, mSessionID
    
    MsgBox "Order " & recordID & " submitted successfully.", vbInformation
    
    ' Reset the form for next entry
    ResetForm
    
    Exit Sub
    
SubmitError:
    MsgBox "Error writing order: " & Err.Description & vbCrLf & _
           "Please contact IT Support with error code " & Err.Number, vbCritical
    WriteAuditEntry "SUBMIT_ERROR", "AllFields", "", Err.Description, "", mSessionID
End Sub

The GetColumnIndex helper makes your code resilient to column reordering — a constant real-world problem when multiple people "improve" a shared workbook:

Private Function GetColumnIndex(tbl As ListObject, colName As String) As Long
    Dim col As ListColumn
    
    For Each col In tbl.ListColumns
        If col.Name = colName Then
            GetColumnIndex = col.Index
            Exit Function
        End If
    Next col
    
    ' Column not found — raise a meaningful error
    Err.Raise vbObjectError + 1001, "GetColumnIndex", _
              "Column '" & colName & "' not found in table '" & tbl.Name & "'."
End Function

And the AllFieldsValid double-check:

Private Function AllFieldsValid() As Boolean
    Dim key As Variant
    AllFieldsValid = True
    For Each key In mValidFields.Keys
        If Not mValidFields(key) Then
            AllFieldsValid = False
            Exit Function
        End If
    Next key
End Function

The ResetForm procedure clears all controls and re-initializes state for the next entry:

Private Sub ResetForm()
    cboRegion.Value = ""
    cboCountry.Clear
    cboCountry.Enabled = False
    cboCategory.Value = ""
    cboSKU.Clear
    cboSKU.Enabled = False
    txtContractValue.Value = ""
    txtPONumber.Value = ""
    txtCloseDate.Value = ""
    txtEmail.Value = ""
    
    ClearAllErrors
    
    ' Reset all validation states
    Dim key As Variant
    For Each key In mValidFields.Keys
        mValidFields(key) = False
    Next key
    
    btnSubmit.Enabled = False
    
    ' New session ID for the next form entry
    mSessionID = Format(Now, "YYYYMMDDHHMMSS") & "_" & Environ("USERNAME")
    
    ' Clear previous-value trackers
    mPrevContractValue = ""
    mPrevPONumber = ""
    mPrevCloseDate = ""
    mPrevEmail = ""
End Sub

---

Performance and Scalability Considerations

For a form used by a small team entering a few hundred records, none of this poses performance concerns. But if you're building something that will be used heavily — dozens of records per hour, many concurrent users on a shared network workbook — a few things matter:

Regex object creation: Creating a VBScript.RegExp object on every keystroke in a _Change event is wasteful. Promote your regex objects to module-level variables, initialize them in UserForm_Initialize, and reuse them. The pattern doesn't change between calls.

Audit log writes: Each WriteAuditEntry call triggers a worksheet write. On a fast local machine this is imperceptible. On a slow network share, it can cause perceptible lag. Consider buffering audit entries in a module-level collection and flushing them to the sheet on submit — this batches the I/O to one moment rather than distributing it through the session.

Shared workbook limits: If multiple people will use this simultaneously, a shared network .xlsm file will cause conflicts. Consider either a single-user entry model (one form at a time, coordination handled externally) or migrating the data layer to a database backend accessed via ADO. For that pattern, see Connecting Excel to External Databases with VBA: SQL Queries, ADO, and Database Automation.

Reference data loading: If your RefData ranges are large, reading them on every form open has real cost. Cache the reference data in module-level arrays loaded once per workbook session, not per form open. The VBA Arrays and Collections for Efficient Data Processing lesson covers exactly this pattern.

---

Hands-On Exercise

Build a functional version of this system using the following scenario: an HR department tracking new hire onboarding tasks.

Your form should capture:

• Department (drop-down: Engineering, Marketing, Finance, Operations)
• Sub-team (dependent: Engineering → Backend, Frontend, DevOps, Data; Marketing → Brand, Demand Gen, Product Marketing; etc.)
• Employee ID (pattern: EMP- followed by exactly 5 digits)
• Start Date (must be a Monday, must be at least 5 business days in the future)
• Manager Email (valid email format)
• Onboarding Template (drop-down based on department: each department has 2–3 templates)

Implement all of the following:

1. Real-time validation on all text fields
2. Dependent drop-downs for Sub-team and Onboarding Template
3. A validation state dictionary tracking all 6 fields
4. Submit button that only enables when all fields pass
5. An audit log capturing the session ID, username, timestamp, and all submitted values
6. A "Clear Form" button that resets everything and generates a new session ID

Extension challenge: Add a "Load Draft" feature. When the form opens, check if a special Drafts table on the RefData sheet contains a draft for the current user (matched by Environ("USERNAME")). If so, offer to pre-populate the form with those values and re-run all validations automatically.

---

Common Mistakes & Troubleshooting

Problem: The Submit button never enables even though all fields look correct.

The most common cause is a mismatch between the keys in mValidFields and the keys you're passing to SetFieldValid. If you initialize the dictionary with key "ContractValue" but call SetFieldValid "contractvalue", VBA's Scripting.Dictionary is case-sensitive by default and will treat them as different keys. The original key remains False forever. Fix: use constants for all dictionary keys, defined in modConstants.

Problem: Dependent drop-down doesn't clear when parent changes.

You called cboCountry.Clear but forgot to call mValidFields("Country") = False. The dictionary still thinks the country is valid from the previous selection. Always invalidate child field state in the parent's Change handler.

Problem: Regex validation is extremely slow during typing.

You're creating a new CreateObject("VBScript.RegExp") on every keypress via _Change. Move the regex object to module level and initialize once. Each _Change call then just calls .Test() on the already-existing object.

Problem: Audit log writes fail with "Permission Denied."

The AuditLog sheet is xlSheetVeryHidden, which doesn't prevent VBA writes — so the issue is something else. Check if you've applied worksheet protection (.Protect) to the audit log sheet. xlSheetVeryHidden and worksheet protection are separate mechanisms. Either remove the protection or use ws.Unprotect before writing and ws.Protect after.

Problem: GetColumnIndex raises an error on submit because a column was renamed.

This is the error telling you your data contract is broken — someone renamed a column header in tblOrders. The error message from GetColumnIndex will name the missing column explicitly. The fix is to restore the column name, not to change the code. This is correct behavior: the form should fail loudly when the data table structure breaks.

Problem: Form shows "User-defined type not defined" for MSForms.Label.

This happens when you reference MSForms.Label in the function signature but the UserForms library isn't referenced. Since you're writing code inside a UserForm module, MSForms should be available automatically. If the error appears, check whether you accidentally pasted the function into a standard module instead of the form's code module.

---

Summary & Next Steps

You've built something genuinely useful here. The validation engine you've constructed follows a dispatcher pattern that separates validation rules from validation state management, which means adding a new field requires only: writing a rule function, adding a key to the dictionary, and wiring up the event handlers. The architecture scales without getting messier.

The dependent drop-down pattern — load child options based on parent selection, always invalidate child state when parent changes — handles the most common real-world data integrity problem in Excel-based data entry. And the audit log gives you a forensic record that's invisible to users but invaluable to administrators.

Where to go from here:

• Harden the UI further by exploring the full range of UserForm controls available to you — VBA UserForm Controls Deep Dive: ComboBoxes, ListBoxes, and MultiPage Widgets covers multi-page form patterns that let you break a complex form into logical sections.

• Add automated tests to your validation functions so regressions don't slip through. Building a VBA Testing Framework for Excel shows you how to unit test VBA code systematically.

• Move the data layer to a database when your concurrency needs outgrow a shared workbook. The ADO pattern in Connecting Excel to External Databases with VBA plugs directly into the submit handler you've already built.

• Package this as a deployable tool using the add-in pattern from Building Excel Add-Ins with VBA, so the form can live separately from the data workbook and be deployed to your whole team without embedding VBA in every data file.

The pattern you've learned here — event-driven validation, centralized state tracking, audit logging — applies well beyond data entry forms. Any time you're building automation that modifies shared data, these architectural decisions are the right ones.