VBA Excel用户表单日期验证与数据写入问题求助

Hey there! I see where the problems are in your code—let's get those date validation and non-empty checks working properly. Your current code has structural errors (like nested event procedures) and logical gaps that are making the validation fail. Here's the corrected code, plus a breakdown of what was fixed:

Corrected Full Code #

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim booValidationPassed As Boolean
    booValidationPassed = True
    
    ' --- Step 1: Non-empty Check (exclude TextBox5, TextBox6) ---
    If Len(Trim(TextBox1.Text)) = 0 Or _
       Len(Trim(TextBox2.Text)) = 0 Or _
       Len(Trim(TextBox3.Text)) = 0 Or _
       Len(Trim(TextBox4.Text)) = 0 Or _
       Len(Trim(TextBox7.Text)) = 0 Or _
       Len(Trim(TextBox8.Text)) = 0 Or _
       Len(Trim(TextBox9.Text)) = 0 Or _
       Len(Trim(TextBox10.Text)) = 0 Or _
       Len(Trim(TextBox11.Text)) = 0 Then
        MsgBox "Please fill in all required fields (TextBox5/6 can be empty)", vbExclamation, "Input Error"
        booValidationPassed = False
    End If
    
    ' --- Step 2: Date Format Validation ---
    If booValidationPassed Then
        ' TextBox3 & TextBox4 are required, must be valid dates
        If Not IsDate(TextBox3.Text) Then
            MsgBox "TextBox3 must be a valid date!", vbCritical, "Date Error"
            TextBox3.SetFocus
            booValidationPassed = False
        ElseIf Not IsDate(TextBox4.Text) Then
            MsgBox "TextBox4 must be a valid date!", vbCritical, "Date Error"
            TextBox4.SetFocus
            booValidationPassed = False
        End If
        
        ' TextBox5 & TextBox6 are optional, but if filled, must be valid dates
        If booValidationPassed Then
            If Len(Trim(TextBox5.Text)) > 0 And Not IsDate(TextBox5.Text) Then
                MsgBox "TextBox5 must be a valid date if filled!", vbCritical, "Date Error"
                TextBox5.SetFocus
                booValidationPassed = False
            ElseIf Len(Trim(TextBox6.Text)) > 0 And Not IsDate(TextBox6.Text) Then
                MsgBox "TextBox6 must be a valid date if filled!", vbCritical, "Date Error"
                TextBox6.SetFocus
                booValidationPassed = False
            End If
        End If
    End If
    
    ' --- Step 3: User Confirmation & Write Data ---
    If booValidationPassed Then
        If MsgBox("Are the entries correct?" & vbCrLf & _
                  TextBox1 & " | " & TextBox2 & " | " & TextBox3 & " | " & TextBox4 & vbCrLf & _
                  TextBox7 & " | " & TextBox8 & " | " & TextBox9 & " | " & TextBox10 & " | " & TextBox11, _
                  vbYesNo + vbQuestion, "Confirm Entries") = vbYes Then
            ' Write data to worksheet
            Set ws = Sheets("Inputs")
            LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row + 1
            
            With ws
                .Range("B" & LastRow).Value = TextBox1.Text
                .Range("C" & LastRow).Value = TextBox2.Text
                .Range("D" & LastRow).Value = TextBox3.Text
                .Range("E" & LastRow).Value = TextBox4.Text
                .Range("F" & LastRow).Value = TextBox5.Text
                .Range("G" & LastRow).Value = TextBox6.Text
                .Range("H" & LastRow).Value = TextBox7.Text
                .Range("I" & LastRow).Value = TextBox8.Text
                .Range("J" & LastRow).Value = TextBox9.Text
                .Range("K" & LastRow).Value = TextBox10.Text
                .Range("L" & LastRow).Value = TextBox11.Text
            End With
            
            MsgBox "Data saved successfully!", vbInformation
            Unload Me
        Else
            MsgBox "Please correct your entries.", vbExclamation
            ' Keep the form open for edits
        End If
    End If
End Sub

' Optional: Add real-time date validation when exiting each date textbox
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(Trim(TextBox3.Text)) > 0 And Not IsDate(TextBox3.Text) Then
        MsgBox "TextBox3 must be a valid date!", vbCritical
        Cancel = True ' Keep focus on the textbox
        TextBox3.SelStart = 0
        TextBox3.SelLength = Len(TextBox3.Text)
    End If
End Sub

Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(Trim(TextBox4.Text)) > 0 And Not IsDate(TextBox4.Text) Then
        MsgBox "TextBox4 must be a valid date!", vbCritical
        Cancel = True
        TextBox4.SelStart = 0
        TextBox4.SelLength = Len(TextBox4.Text)
    End If
End Sub

Private Sub TextBox5_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(Trim(TextBox5.Text)) > 0 And Not IsDate(TextBox5.Text) Then
        MsgBox "TextBox5 must be a valid date if filled!", vbCritical
        Cancel = True
        TextBox5.SelStart = 0
        TextBox5.SelLength = Len(TextBox5.Text)
    End If
End Sub

Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(Trim(TextBox6.Text)) > 0 And Not IsDate(TextBox6.Text) Then
        MsgBox "TextBox6 must be a valid date if filled!", vbCritical
        Cancel = True
        TextBox6.SelStart = 0
        TextBox6.SelLength = Len(TextBox6.Text)
    End If
End Sub

Key Fixes & Explanations #

1. Removed Nested Event Procedure
   Your original code had TextBox3_Exit inside CommandButton1_Click—this is invalid VBA syntax. Event procedures must be standalone, so I moved the date validation to separate Exit events (for real-time checks) and also included a final check in the button click event.

2. Fixed Non-empty Validation Logic

   • Excluded TextBox5 and TextBox6 from the non-empty check (per your requirement)
   • Used Trim() to ignore whitespace (so users can't just enter spaces)
   • Set a boolean flag (booValidationPassed) to stop further processing if any check fails

3. Improved Date Validation

   • Split validation into required (TextBox3, TextBox4) and optional (TextBox5, TextBox6)
   • For optional boxes: only validate if there's input (empty is allowed)
   • Focuses the cursor on the invalid textbox to make corrections easier

4. Streamlined Confirmation & Write Logic

   • Only show the confirmation dialog if all validations pass
   • Avoided duplicate worksheet write code by using a With block
   • Doesn't try to empty worksheet rows when user selects "No" (since no data was written yet)
   • Only closes the form after successful data save

5. Added Real-Time Validation
   The separate Exit events for each date textbox catch invalid dates as soon as the user leaves the field, instead of waiting for the button click—this gives a better user experience.

内容的提问来源于stack exchange，提问作者Mariana Ferreira