UserForm Data Entry System

' UserForm Data Entry System
' This code creates a data entry form with cascading dropdowns and validation

Private Sub UserForm_Initialize()
    ' Initialize form controls
    On Error GoTo ErrorHandler

    ' Populate dropdown from worksheet data
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("MasterData")

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Populate ComboBox1 with unique values from column A
    Dim i As Long
    For i = 2 To lastRow
        If ws.Cells(i, 1).Value <> "" Then
            Me.ComboBox1.AddItem ws.Cells(i, 1).Value
        End If
    Next i

    ' Set default values
    Me.TextBox1.Value = ""
    Me.TextBox2.Value = ""
    Me.ComboBox2.Clear

    Exit Sub

ErrorHandler:
    MsgBox "Error initialising form: " & Err.Description, vbCritical
End Sub

Private Sub ComboBox1_Change()
    ' Cascade: populate ComboBox2 based on ComboBox1 selection
    On Error GoTo ErrorHandler

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("MasterData")

    Dim selectedValue As String
    selectedValue = Me.ComboBox1.Value

    ' Clear dependent dropdown
    Me.ComboBox2.Clear

    ' Find matching records and populate ComboBox2
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    Dim i As Long
    For i = 2 To lastRow
        If ws.Cells(i, 1).Value = selectedValue Then
            If ws.Cells(i, 2).Value <> "" Then
                Me.ComboBox2.AddItem ws.Cells(i, 2).Value
            End If
        End If
    Next i

    Exit Sub

ErrorHandler:
    MsgBox "Error updating dependent field: " & Err.Description, vbCritical
End Sub

Private Sub CommandButton1_Click()
    ' Submit button - validate and save data
    On Error GoTo ErrorHandler

    ' Validate required fields
    If Me.ComboBox1.Value = "" Then
        MsgBox "Please select a value from the first dropdown.", vbExclamation
        Exit Sub
    End If

    If Me.ComboBox2.Value = "" Then
        MsgBox "Please select a value from the second dropdown.", vbExclamation
        Exit Sub
    End If

    If Me.TextBox1.Value = "" Then
        MsgBox "Please enter a value in the text field.", vbExclamation
        Me.TextBox1.SetFocus
        Exit Sub
    End If

    ' Save data to worksheet
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("DataEntry")

    Dim nextRow As Long
    nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

    ' Write data to columns
    ws.Cells(nextRow, 1).Value = Me.ComboBox1.Value
    ws.Cells(nextRow, 2).Value = Me.ComboBox2.Value
    ws.Cells(nextRow, 3).Value = Me.TextBox1.Value
    ws.Cells(nextRow, 4).Value = Me.TextBox2.Value
    ws.Cells(nextRow, 5).Value = Now ' Timestamp
    ws.Cells(nextRow, 6).Value = Application.UserName ' User who entered data

    MsgBox "Data saved successfully!", vbInformation

    ' Clear form for next entry
    Call UserForm_Initialize

    Exit Sub

ErrorHandler:
    MsgBox "Error saving data: " & Err.Description, vbCritical
End Sub

Private Sub CommandButton2_Click()
    ' Cancel button - close form
    Unload Me
End Sub