如何设置Excel VBA UserForm所有字段为必填项后再提交？

核心修改思路 #

1. 遍历UserForm中所有TextBox和ComboBox控件，包括MultiPage各页面内的子控件
2. 检查每个控件是否为空（含空格判断），若存在未填写字段则提示并终止提交流程
3. 校验完全通过后，再执行数据写入和控件清空操作

修改后的提交按钮事件代码 #

Private Sub ADDPROPERTYIMAGE2_Click()

    Dim intC As Integer
    Dim intB As Integer
    Dim Ctrl As Control
    Dim PageCtrl As Control
    Dim isAllFilled As Boolean
    isAllFilled = True

    intC = addproperty.Range("A2").Value
    intB = addproperty.Range("F2").Value

    ' 1. 校验所有TextBox和ComboBox（含MultiPage页面内的控件）
    ' 遍历UserForm根控件
    For Each Ctrl In Me.Controls
        ' 处理MultiPage控件，遍历其下所有页面的子控件
        If TypeName(Ctrl) = "MultiPage" Then
            For Each PageCtrl In Ctrl.Pages
                For Each PageCtrl In PageCtrl.Controls
                    If TypeName(PageCtrl) = "TextBox" And Trim(PageCtrl.Value) = "" Then
                        MsgBox "字段 [" & PageCtrl.Name & "] 不能为空，请填写完整！", vbExclamation, "THY PROPERTIES"
                        PageCtrl.SetFocus ' 自动定位到空字段
                        isAllFilled = False
                        Exit For
                    End If
                    If TypeName(PageCtrl) = "ComboBox" And Trim(PageCtrl.Value) = "" Then
                        MsgBox "字段 [" & PageCtrl.Name & "] 不能为空，请填写完整！", vbExclamation, "THY PROPERTIES"
                        PageCtrl.SetFocus ' 自动定位到空字段
                        isAllFilled = False
                        Exit For
                    End If
                Next PageCtrl
                If Not isAllFilled Then Exit For
            Next
        Else
            ' 处理非MultiPage的TextBox和ComboBox
            If TypeName(Ctrl) = "TextBox" And Trim(Ctrl.Value) = "" Then
                MsgBox "字段 [" & Ctrl.Name & "] 不能为空，请填写完整！", vbExclamation, "THY PROPERTIES"
                Ctrl.SetFocus
                isAllFilled = False
                Exit For
            End If
            If TypeName(Ctrl) = "ComboBox" And Trim(Ctrl.Value) = "" Then
                MsgBox "字段 [" & Ctrl.Name & "] 不能为空，请填写完整！", vbExclamation, "THY PROPERTIES"
                Ctrl.SetFocus
                isAllFilled = False
                Exit For
            End If
        End If
        If Not isAllFilled Then Exit For
    Next Ctrl

    ' 校验不通过则终止流程
    If Not isAllFilled Then Exit Sub

    ' 2. 校验通过，写入数据到工作表
    ' 写入TextBox数据
    addproperty.Range("A" & intC + 4).Value = TextBoxPROPRef.Value
    addproperty.Range("B" & intC + 4).Value = TextBoxPROPDoor.Value
    addproperty.Range("C" & intC + 4).Value = TextBoxPROPStreet.Value
    addproperty.Range("D" & intC + 4).Value = TextBoxPROPTown.Value
    addproperty.Range("E" & intC + 4).Value = TextBoxPROPPostcode.Value
    addproperty.Range("I" & intC + 4).Value = TextBoxPROPAVALDate.Value
    addproperty.Range("L" & intC + 4).Value = TextBoxPROPRent.Value
    addproperty.Range("W" & intC + 4).Value = TXTPROPNotes.Value

    ' 写入ComboBox数据
    addproperty.Range("F" & intB + 4).Value = ComboBoxPROPStatus.Value
    addproperty.Range("G" & intB + 4).Value = ComboBoxPROPType.Value
    addproperty.Range("H" & intB + 4).Value = ComboBoxPROPFee.Value
    addproperty.Range("J" & intB + 4).Value = ComboBoxPROPVIEWARNG.Value
    addproperty.Range("K" & intB + 4).Value = ComboBoxPROPTenantType.Value
    addproperty.Range("M" & intB + 4).Value = ComboBoxPROPLRoom.Value
    addproperty.Range("N" & intB + 4).Value = ComboBoxPROPBRoom.Value
    addproperty.Range("O" & intB + 4).Value = ComboBoxPROPKitchen.Value
    addproperty.Range("P" & intB + 4).Value = ComboBoxPROPBathroom.Value
    addproperty.Range("Q" & intB + 4).Value = ComboBoxPROPGarden.Value
    addproperty.Range("R" & intB + 4).Value = ComboBoxPROPOfferedas.Value
    addproperty.Range("S" & intB + 4).Value = ComboBoxEICR.Value
    addproperty.Range("T" & intB + 4).Value = ComboBoxGAS.Value
    addproperty.Range("U" & intB + 4).Value = ComboBoxEPC.Value
    addproperty.Range("V" & intB + 4).Value = ComboBoxLICENSE.Value

    ' 3. 清空所有TextBox和ComboBox
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "MultiPage" Then
            For Each PageCtrl In Ctrl.Pages
                For Each PageCtrl In PageCtrl.Controls
                    If TypeName(PageCtrl) = "TextBox" Then PageCtrl.Value = ""
                    If TypeName(PageCtrl) = "ComboBox" Then PageCtrl.Value = ""
                Next PageCtrl
            Next
        Else
            If TypeName(Ctrl) = "TextBox" Then Ctrl.Value = ""
            If TypeName(Ctrl) = "ComboBox" Then Ctrl.Value = ""
        End If
    Next Ctrl

    ThisWorkbook.Save

End Sub

关键细节说明 #

• MultiPage控件处理：直接遍历Me.Controls无法获取MultiPage页面内的子控件，因此需要嵌套遍历MultiPage的每个Page及其下的控件
• 空格过滤：使用Trim(Ctrl.Value)避免用户输入的空格被误判为有效内容
• 快速定位：通过SetFocus自动跳转到未填写的字段，提升用户操作效率
• 流程控制：用isAllFilled布尔变量标记校验状态，一旦发现空字段立即终止遍历并提示

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