excel - 使用循环验证用户窗体以检查空控件

Question

用户输入数据后。当用户点击提交按钮时。我将检查所有文本框是否不为空。
用户输入成员(member)编号，如果用户输入1个成员(member)我会检查member01是否为空，如果用户输入3个成员(member)我会检查member01, member02, member03是否为空
它适用于 If Else 但我想为循环做它，因为我做 10 次很乏味。我不知道如何将其从 If Else 更改为 For Loop。

 ‘Using If Else
  If txtNoMember.Value = "" Then
        MsgBox "Please enter the Number of Member.", vbExclamation, "Input Data"
        txtNoMember.SetFocus
        Exit Sub
    End If

    If txtNoMember.Value = 1 And txtMember01.Value = "" Then
        MsgBox "Member cannot be empty.", vbExclamation, "Input Data"
        Exit Sub
    End If

    If txtNoMember.Value = 2 And txtMember01.Value = "" And txtMember02.Value = "" Then
        MsgBox "Member(s) cannot be empty.", vbExclamation, "Input Data"
        Exit Sub
    ElseIf txtNoMember.Value = 2 And txtMember01.Value = "" Then
        MsgBox "Member(s) cannot be empty.", vbExclamation, "Input Data"
        Exit Sub
    ElseIf txtNoMember.Value = 2 And txtMember02.Value = "" Then
        MsgBox "Member(s) cannot be empty.", vbExclamation, "Input Data"
        Exit Sub
    End If

    If txtNoMember.Value = 3 And txtMember01.Value = "" Then
        MsgBox "Member(s) cannot be empty.", vbExclamation, "Input Data"
        Exit Sub
    ElseIf txtNoMember.Value = 3 And txtMember02.Value = "" Then
        MsgBox "Member(s) cannot be empty.", vbExclamation, "Input Data"
        Exit Sub
    ElseIf txtNoMember.Value = 3 And txtMember03.Value = "" Then
        MsgBox "Member(s) cannot be empty.", vbExclamation, "Input Data"
        Exit Sub
        MsgBox "Member(s) cannot be empty.", vbExclamation, "Input Data"
        Exit Sub
    End If

‘Using Loop
Dim Ctrl As Control
Dim CtrlNum As Long

For Each Ctrl In Me.Controls
    If Ctrl.Name Like "txtMember##" Then
        CtrlNum = CLng(Right$(Ctrl.Name, 2))
        If CtrlNum.Value = "" Then
            MsgBox "Member(s) cannot be empty.", vbExclamation, "Input Data"
        End If
    End If
Next

Answer 1

你可以这样做:

Private Sub CommandButton1_Click()
If txtNoMember.Value = "" Then
    MsgBox "Please enter the Number of Member.", vbExclamation, "Input Data"
    Exit Sub
Else
    Dim v As Long, ctrl As Control
    v = txtNoMember.Value
    For i = 1 To v
        For Each ctrl In Controls ' loop through Controls and search for Control with the right name
            If ctrl.Name = "txtMember" & Format(i, "0#") Then
                If ctrl.Value = "" Then
                    MsgBox "Member(s) cannot be empty.", vbExclamation, "Input Data"
                    Exit Sub
                End If
                Exit For
            End If
        Next
    Next
End Sub

即使您在 txtNoMember 中输入 4，当您只有 txtMemberNo03 作为最大值时，这也有效。