Close UserForm if All Data Captured

Question

Say you have aUserForm with TextBox1, TextBox3, TextBox3 and an OK Button.

To only allow the UserForm to close if all three TextBox have data I would use the following script assigned to the OK Button:

Private Sub CommandButton1_Click()

    If Len(TextBox1.Value) >= 1 And _
        Len(TextBox2.Value) >= 1 And _
            Len(TextBox3.Value) >= 1 Then

        Me.Hide
    Else
        MsgBox "Please Complete All Fields!"
    End If

End Sub

Is there another way to do this besides an If statement?

Answer 1

Direct User Before Errors Are Made

Preferable to informing a user after an invalid action has been made is to prevent the user from performing that invalid action in the first place[1]. One way to do this is to use the Textbox_AfterUpdate event to call a shared validation routine that controls the Enabled property of your OK button, and also controls the display of a status label. The result is a more informative interface that only allows valid actions, thereby limiting the nuisance of msgbox popups. Here's some example code and screenshots.

Private Sub TextBox1_AfterUpdate()
    RunValidation
End Sub
Private Sub TextBox2_AfterUpdate()
    RunValidation
End Sub
Private Sub TextBox3_AfterUpdate()
    RunValidation
End Sub
Private Sub RunValidation()
    If Len(TextBox1.Value) = 0 Or Len(TextBox2.Value) = 0 Or Len(TextBox3.Value) = 0 Then
        CommandButton1.Enabled = False
        Label1.Visible = True
    Else
        CommandButton1.Enabled = True
        Label1.Visible = False
    End If
End Sub
Private Sub CommandButton1_Click()
    Me.Hide
End Sub

The If Statement

As far as the If statement is concerned, there are a ton of ways that can be done, but I think anything other than directly evaluating TextBox.Value leads to unnecessary plumbing and code complexity, so I think it's hard to argue for anything other than the If statement in the OP. That being said, this particular If statement can be slightly condensed by capitalizing on its numeric nature, which allows for

Len(TextBox1.Value) = 0 Or Len(TextBox2.Value) = 0 Or Len(TextBox3.Value) = 0

to be replaced with

Len(TextBox1.Value) * Len(TextBox2.Value) * Len(TextBox3.Value) = 0 

Although that doesn't gain you much and is arguably less readable code, it does allow for a condensed one liner, especially if the textboxes are renamed...

If Len(TB1.Value) * Len(TB2.Value) * Len(TB3.Value) = 0 Then  

.Value vs .Text

Lastly, in this case, I think .Value should be used instead of .Text. .Text is more suited for validating a textbox entry while its being typed, but in this case, you're looking to validate a textbox's saved data, which is what you get from .Value.

More User feedback - Colorization

I almost forgot, I wanted to include this example of how to include even more user feedback. There is a balance between providing useful feedback and overwhelming with too much. This is especially true if the overall form is complicated, or if the intended user has preferences, but color indication for key fields is usually beneficial. A lot of applications may present the form without color at first and then colorize it if the user is having trouble.

Private InvalidColor
Private ValidColor
Private Sub UserForm_Initialize()
    InvalidColor = RGB(255, 180, 180)
    ValidColor = RGB(180, 255, 180)
    TextBox1.BackColor = InvalidColor
    TextBox2.BackColor = InvalidColor
    TextBox3.BackColor = InvalidColor
End Sub
Private Sub TextBox1_AfterUpdate()
    RunValidation Me.ActiveControl
End Sub
Private Sub TextBox2_AfterUpdate()
    RunValidation Me.ActiveControl
End Sub
Private Sub TextBox3_AfterUpdate()
    RunValidation Me.ActiveControl
End Sub
Private Sub RunValidation(ByRef tb As MSForms.TextBox)
    If Len(tb.Value) > 0 Then
        tb.BackColor = ValidColor
    Else
        tb.BackColor = InvalidColor
    End If

    If Len(TextBox1.Value) * Len(TextBox2.Value) * Len(TextBox3.Value) = 0 Then
        CommandButton1.Enabled = False
        Label1.Visible = True
    Else
        CommandButton1.Enabled = True
        Label1.Visible = False
    End If
End Sub
Private Sub CommandButton1_Click()
    Me.Hide
End Sub

Answer 2

As I said in my comment, that is an ok way to do it. But i'll post this just so you have an example of another way. This would allow you to evaluate what is going into the text boxes as they are set.

Option Explicit

Dim bBox1Value As Boolean
Dim bBox2Value As Boolean
Dim bBox3Value As Boolean

Private Sub TextBox1_Change()
    If Trim(TextBox1.Text) <> "" Then
        bBox1Value = True
    End If
End Sub

Private Sub TextBox2_Change()
    If Trim(TextBox2.Text) <> "" Then
        bBox2Value = True
    End If
End Sub

Private Sub TextBox3_Change()
    If Trim(TextBox3.Text) <> "" Then
        bBox3Value = True
    End If
End Sub

Private Sub CommandButton1_Click()

    If bBox1Value = True And bBox2Value = True And bBox3Value = True Then
        Me.Hide
    Else
        MsgBox "Please Complete All Fields!"
    End If

End Sub

Answer 3

You can use a loop:

Private Sub CommandButton1_Click()
   Dim n as long
   For n = 1 to 3
    If Len(Trim(Me.Controls("TextBox" & n).Value)) = 0 Then
        MsgBox "Please Complete All Fields!"
        Exit Sub
    End If
   Next n
   Me.Hide

End Sub

Answer 4

You can use the below code

 Private Sub CommandButton1_Click()

        If Trim(TextBox1.Value & vbNullString) = vbNullString And _
            Trim(TextBox2.Value & vbNullString) = vbNullString And _
               Trim(TextBox3.Value & vbNullString) = vbNullString Then

            Me.Hide
        Else
            MsgBox "Please Complete All Fields!"
        End If

    End Sub

I got the answer from this question VBA to verify if text exists in a textbox, then check if date is in the correct format