VBA code checks if TextBox is empty, if so does not allow user to continue

Question

I have a userform with multiple text boxes. I would like a message box to appear if there is something in either TextBox2 and/or TextBox3, but nothing in TextBox1. I would also like it if the user was not able to continue if this occurs.

This is my current code:

Private Sub SubmitCommandButtom_Click()
If Len(TextBox1) = "" And Len(TextBox2) > 0 Or Len(TextBox3) > 0 Then
    MsgBox "Unit Number must be entered to continue!"
End If
Sheets("Uneven Split Job Aid").Range("A2") = TextBox1.Value
Sheets("Uneven Split Job Aid").Range("B2") = TextBox2.Value
Sheets("Uneven Split Job Aid").Range("C2") = TextBox3.Value

This code currently only generates the message box when there is a value in Textbox3 and not in TextBox1, it doesn't matter if anything is in TextBox2. I would like it to check TextBox2 for a value as well. Also, when I click Ok on the message box, the Sub continues to run and inserts the values of the TextBoxes onto the worksheet. I would like if that did not happen if a message box appears.

Thanks for any help in advance!

Mistella Mistella · Accepted Answer · 2018-04-26T15:52:01

When using And/Or Boolean logic, sometimes parentheses are required to ensure it is evaluated correctly. When it was:

If Con1 And Con2 Or Con3 Then

It was being being interpreted:

Are both Con1 And Con2 True?

Or

Is Con3 True?

So, I added parentheses to read:

If Con1 And (Con2 Or Con3) Then

Which will be interpreted as:

Is Con1 True?

And

Is either Con2 or Con3 True?

Which, if I understood your question correctly, is the logic you're aiming for. However, this does mean that if both TextBox2 and Textbox3 are empty, then it won't matter if TextBox1 has anything; the MsgBox won't happen, and the rest of the code will.

Further Boolean Analysis:

Since Con1 -- Len(TextBox1) = "" -- was always resolving to False (see Note 1 below), the And check -- Con1 And Con2 was always false. Thus, the only way that the if statement could resolve to true was if Con3 resolved to True.

Since Con3 was Len(TextBox3) > 0, the entire if statement was dependent on the length of the text in TextBox3!

Notes:

Len(TextBox1) = "" will always equal false. Len() returns a number not a string. I think you acidentally combined two methods for checking for an empty string. Either TextBox1.Value = "" or Len(TextBox1.Value) = 0 would work; however, the combination doesn't make sense. Although it took me most of typing up my answer before I caught this.
It's better practice to use either TextBox.Value or TextBox.Text instead of just TextBox when accessing the contents.
The current check you have for the contents of TextBox1 will cause the MsgBox only when TextBox1 is empty. This excludes white space characters, such as a space. If you also want the MsgBox when there are only white space characters, you should try something like: Len(Trim(TextBox1.Value)) = 0 instead of what you had previously (Len(TextBox1) = "").

You can look at other suggestions for this here You might also want to consider adding a similar check onto TextBox2 and TextBox3 if you don't want to trigger the MsgBox if they have white space characters, but not any non-white-space characters.

Private Sub SubmitCommandButtom_Click()

'Changed Len(TextBox1) = "" to Len(TextBox1.Value) = 0 as per explanation above.
'Added .Value to length checks
If Len(TextBox1.Value) = 0 And (Len(TextBox2.Value) > 0 Or Len(TextBox3.Value) > 0) Then
    MsgBox "Unit Number must be entered to continue!"
Else
    Sheets("Uneven Split Job Aid").Range("A2") = TextBox1.Value
    Sheets("Uneven Split Job Aid").Range("B2") = TextBox2.Value
    Sheets("Uneven Split Job Aid").Range("C2") = TextBox3.Value
    'And any other code ....
End If

VBA code checks if TextBox is empty, if so does not allow user to continue

2 Answers