Excel VBA用户表单验证需求：TextBox1-5未填禁止提交数据

问题说明 #

我正在创建一个包含7个TextBox的Excel UserForm，每个TextBox的内容要写入名为“Bulk Loader”的工作表对应列。需要添加校验逻辑：若TextBox1至TextBox5中的任意一个未填写，则禁止向工作表提交数据。以下是我的现有代码：

Private Sub CommandButton1_Click()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Bulk Loader")
Dim n As Long

n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row

sh.Range("A" & n + 1).Value = TextBox1.Value
sh.Range("B" & n + 1).Value = TextBox2.Value
sh.Range("C" & n + 1).Value = TextBox3.Value
sh.Range("D" & n + 1).Value = TextBox4.Value
sh.Range("E" & n + 1).Value = TextBox5.Value
sh.Range("F" & n + 1).Value = TextBox6.Value
sh.Range("G" & n + 1).Value = TextBox7.Value

 If TextBox1.Value <> "" And TextBox2.Value <> "" And TextBox3.Value <> "" And TextBox4.Value <> "" And TextBox5.Value <> "" And TextBox6.Value <> "" And TextBox7.Value <> "" Then
    
    MsgBox "IPO Details Added", vbOKOnly + vbInformation, "ERROR"
    
    Unload Me
    
    Exit Sub
    
    End If
    
    If TextBox1.Value = "" Then
        MsgBox "User ID can't be filled blank", vbOKOnly + vbCritical, "ERROR"
        
    End If
    
    If TextBox2.Value = "" Then
        MsgBox "Please enter Launch Date", vbOKOnly + vbCritical, "ERROR"
        
    End If
    
    If TextBox3.Value = "" Then
        MsgBox "Please input Date", vbOKOnly + vbCritical, "ERROR"
        
    End If
    
    If TextBox4.Value = "" Then
        MsgBox "Please input Price", vbOKOnly + vbCritical, "ERROR"
        
    End If
    
    If TextBox5.Value = "" Then
        MsgBox "Please enter Launch Price ", vbOKOnly + vbCritical, "ERROR"
        
    End If
       
    Exit Sub
    
    If response = "vbOKOnly" Then DATA_ENTRY.Show
    
    Exit Sub
    
End Sub

现有代码的问题 #

• 校验顺序错误：先写入数据再做校验，导致空数据已被存入工作表
• 校验条件不符需求：原代码要求7个TextBox全部非空才允许提交，但实际仅需TextBox1-5必填
• 冗余无效代码：response变量未定义，且Exit Sub后的代码永远不会执行

修正后的代码 #

Private Sub CommandButton1_Click()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Bulk Loader")
    Dim n As Long
    
    ' 先校验必填项（TextBox1至TextBox5）
    If Trim(TextBox1.Value) = "" Then
        MsgBox "User ID不能为空", vbOKOnly + vbCritical, "错误提示"
        TextBox1.SetFocus ' 自动定位到未填写的控件
        Exit Sub
    End If
    
    If Trim(TextBox2.Value) = "" Then
        MsgBox "请输入Launch Date", vbOKOnly + vbCritical, "错误提示"
        TextBox2.SetFocus
        Exit Sub
    End If
    
    If Trim(TextBox3.Value) = "" Then
        MsgBox "请输入Date", vbOKOnly + vbCritical, "错误提示"
        TextBox3.SetFocus
        Exit Sub
    End If
    
    If Trim(TextBox4.Value) = "" Then
        MsgBox "请输入Price", vbOKOnly + vbCritical, "错误提示"
        TextBox4.SetFocus
        Exit Sub
    End If
    
    If Trim(TextBox5.Value) = "" Then
        MsgBox "请输入Launch Price", vbOKOnly + vbCritical, "错误提示"
        TextBox5.SetFocus
        Exit Sub
    End If
    
    ' 校验通过后执行数据写入
    n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row
    
    sh.Range("A" & n + 1).Value = TextBox1.Value
    sh.Range("B" & n + 1).Value = TextBox2.Value
    sh.Range("C" & n + 1).Value = TextBox3.Value
    sh.Range("D" & n + 1).Value = TextBox4.Value
    sh.Range("E" & n + 1).Value = TextBox5.Value
    sh.Range("F" & n + 1).Value = TextBox6.Value ' 可选字段，允许为空
    sh.Range("G" & n + 1).Value = TextBox7.Value ' 可选字段，允许为空
    
    ' 提示成功并关闭窗体
    MsgBox "IPO详情已添加", vbOKOnly + vbInformation, "操作成功"
    Unload Me
End Sub

优化点说明 #

1. 调整执行顺序：先完成必填项校验，通过后再写入数据，避免空数据流入工作表
2. 添加空格过滤：用Trim()函数排除用户仅输入空格的无效内容
3. 优化用户体验：校验不通过时自动定位到未填写的TextBox，方便用户补填
4. 简化逻辑结构：移除冗余无效代码，明确区分必填/可选字段
5. 修正提示文案：调整提示框标题与内容，避免语义混淆

内容的提问来源于stack exchange，提问作者Jc Vivo