Search code examples
exceluserformvba

How come IsNull(), IsEmpty(), Application.WorksheetFunction.Istext() does not work on combo boxes?

I have tried the several variations below and even if I do not select or type anything in the combo box, the MsgBox never runs.

Variation 1:

Private Sub CommandButton1_Click()

If IsNull(cmbPaidTo.Text) = True Then
    MsgBox "Payee cannot be empty."
End If

Unload Me
UserForm1.Show

End Sub

Variation 2:

Private Sub CommandButton1_Click()

If IsNull(cmbPaidTo) = True Then
    MsgBox "Payee cannot be empty."
End If

Unload Me
UserForm1.Show

End Sub

Variation 3:

Private Sub CommandButton1_Click()

If IsEmpty(cmbPaidTo.Text) = True Then
    MsgBox "Payee cannot be empty."
End If

Unload Me
UserForm1.Show

End Sub

Variation 4:

Private Sub CommandButton1_Click()

If IsEmpty(cmbPaidTo) = True Then
    MsgBox "Payee cannot be empty."
End If

Unload Me
UserForm1.Show

End Sub

Variation 5:

Private Sub CommandButton1_Click()

If Application.WorksheetFunction.IsText(cbxPaidTo.Text) = False Then
    MsgBox "Payee cannot be empty."
End If

Unload Me
UserForm1.Show

End Sub

Variation 6:

Private Sub CommandButton1_Click()

If Application.WorksheetFunction.IsText(cbxPaidTo) = False Then
    MsgBox "Payee cannot be empty."
End If

Unload Me
UserForm1.Show

End Sub

The combo box should never be empty when the form is submitted, but I cannot understand why I can't get it to work.

This is what my UserForm looks like: UserForm

The combobox i'm pointing at is the one without a text label.

Solution

    • IsNull() should be used only for Access queries. The value of the input box cannot be Null.

    • IsEmpty() - Returns a Boolean value indicating whether avariable has been initialized. Mainly used for ranges and cells in Excel. MSDN. The cbxPaidTo.Text cannot be Empty, because it is an initialized object.

    • .IsText() - here the things are a bit different. Even the empty string "" is still considered text. And whenever you have nothing in your form, it is returning an empty string. Check this:

    Sub TestMe()
    Debug.Print WorksheetFunction.IsText("")
End Sub

    A possible solution is to check the size of the input (as mentioned here) , after Trimming. Like this:

    If Trim(Len(cmbPaidTo) Then