Test several text boxes at once for any blanks

Question

I want to check three different textboxes on a form (but not all) to see if any are left blank. Comparable to "If IsBlank," on the spreadsheet. From what I've read, it seems that IsEmpty can't be used this way? I've been playing with IsNull, but haven't found a proper syntax that would allow it to work. Surely there must be some simple, even standard, way of doing this? Maybe some other function I've never heard of?

(I know I can use If Txtbx1.value = "" Or If... (etc.) —I'm looking for a shorter and more graceful way to do this.)

Thanks!

Answer 1

Match vs Array of Text Boxes feat. IsError, VarType and TypeName

All codes were in a user form code sheet and were run via command buttons on the user form where also the three text boxes were located.

In the first code, the result of Match is passed to the var (variant) variable and further evaluated. If there is at least one text box with no value ("" or vbNullString), var will return the position of the first found empty text box 1-based i.e. the first is 1, the second is 2 etc. unlike the Array which is 0-based i.e. the first element is 0, the second is 1 etc.

The second code is a presentation of the three choices that were studied in the first code.

The third code is a 'bad' code without variables you might be looking for.

Sub TextBoxFun()

    Dim vntTB As Variant    ' Text Box Array
    Dim var As Variant      ' Match Variant
    Dim strTB As String     ' Pass String
    Dim lngTB As Long       ' Pass Long

    ' Pass TextBoxes to Text Box Array.
    vntTB = Array(TextBox1, TextBox2, TextBox3)
    ' Either:
    var = Application.Match("", vntTB, 0)
    ' Or:
    'var = Application.Match(vbNullString, vntTB, 0)

                                            Debug.Print String(10, "'")
    Debug.Print "IsError(var)  = " & IsError(var)    ' True
    Debug.Print "VarType(var)  = " & VarType(var)    ' 10 or vbError
    Debug.Print "TypeName(var) = " & TypeName(var)   ' Error
                                            Debug.Print String(10, "'")

    ' Line of Code / vbNullString Found ? >>> '  True        False
    Debug.Print var                           '     1
    ' Depending on the first position of      '     2
    ' the found vbNullString or "".           '     3   Error 2042
    lngTB = IsError(var): Debug.Print lngTB   '     0           -1
    lngTB = VarType(var): Debug.Print lngTB   '     5           10
    'lngTB = TypeName(var): Debug.Print lngTB '  Nope         Nope
    ' TypeName returns always a string.
    strTB = IsError(var): Debug.Print strTB   ' False         True
    strTB = VarType(var): Debug.Print strTB   '     5           10
    strTB = TypeName(var): Debug.Print strTB  ' Double       Error

End Sub

Sub TextBoxFunConclusion()

    Dim vntTB As Variant    ' Text Box Array

    ' Pass TextBoxes to Text Box Array.
    vntTB = Array(TextBox1, TextBox2, TextBox3)

    If IsError(Application.Match("", vntTB, 0)) Then
        Debug.Print "No 'empty' text boxes (via IsError)."
    Else
        Debug.Print "At least one 'empty' text box (via IsError)."
    End If

    If VarType(Application.Match("", vntTB, 0)) = 10 Then
        Debug.Print "No 'empty' text boxes (via VarType)."
    Else
        Debug.Print "At least one 'empty' text box (via VarType)."
    End If

    If TypeName(Application.Match("", vntTB, 0)) = "Error" Then
        Debug.Print "No 'empty' text boxes (via TypeName)."
    Else
        Debug.Print "At least one 'empty' text box (via TypeName)."
    End If

End Sub

Sub TextBoxFunMyChoice()

    If IsError(Application.Match("", Array(TextBox1, TextBox2, TextBox3), 0)) _
      Then
        Debug.Print "No 'empty' text boxes (via IsError)."
    Else
        Debug.Print "At least one 'empty' text box (via IsError)."
    End If

End Sub



Private Sub CommandButton1_Click()
    TextBoxFun
End Sub

Private Sub CommandButton2_Click()
    TextBoxFunConclusion
End Sub

Private Sub CommandButton3_Click()
    TextBoxFunMyChoice
End Sub