VBA function for checking empty combobox or textbox with complex condition

Question

This is my first time asking a question here, so I hope I am not missing or ignoring any of the posting rules.

Here is my qustion: I have a function that checks through a series of entry boxes, in this case comboboxes and textboxes to see that the user has entered a value. Basically it checks If (TextBox.value & "") = "" and returns False if it finds any of them.

I would like to modify this code so that it returns true if I have values in Textbox1 and Combobox1, or if I have values in AAX and AAY. I only need two of the four to make my calculation. I've tried replacing the Or functions with And functions but it doesn't seem to change the functionality of the code.

Thanks in advance for any help!

Code for the function:

Function validate() As Boolean

    Dim ret As Boolean

    If (TextBox1.Value & "") = "" Or (ComboBox1.Value & "") = "" Or (AAX.Value & "") = "" Or (AAY.Value & "") = "" Or (ComboBox2.Value & "") = "" _
        Or (ComboBox3.Value & "") = "" Or (RB.Value & "") = "" Or (MFC.Value & "") = "" Or (MCC.Value & "") = "" Or (LB.Value & "") = "" _
        Or (TB.Value & "") = "" Or (BB.Value & "") = "" Or (ComboBox4.Value & "") = "" _
        Then
            ret = False

        Else
            ret = True

    End If

    validate = ret

End Function

Code that calls the function:

Private Sub CommandButton1_Click()

    If Not validate() Then
       MsgBox "Fill all required fields, please"
       Cancel = True
    End If

End Sub

To start with you don't need the `& ""` You can just do `If TextBox1.Value = "" Or — MatthewD, Oct 21 '15 at 17:01
do you care about the other boxes anymore? or just the 4 you referenced? — Scott Holtzman, Oct 21 '15 at 17:19

score 0 · Answer 1 · edited May 23 '17 at 12:14

From what I understand you want to return true if any two items have a value.

Basically you will want If (item1 <> "" And item2 <> "") Or (item3 <>"" And item4 <> "") Then

You can try something like this:

If (TextBox1.Value <> "" And ComboBox1.Value <> "") Or _
   (AAX.Value <> "" And AAY.Value <> "") Or _
   (ComboBox2.Value <> "" And ComboBox3.Value <> "") Or _ 
   (RB.Value <> "" And MFC.Value <> "") Or _
   (MCC.Value <> "" And LB.Value <> "") Or _  
   (TB.Value <> "" And BB.Value <> "") Then
        ret = False

    Else
        ret = True

End If

If you want to return true if ANY two have a value you will need a different approach. Let me know and I can provide that if it is that case.

Make note that an items .Value property may differ from its .Text property. What is the difference between .text, .value, and .value2?

score 0 · Answer 2 · answered Oct 21 '15 at 17:33

I made the assumption from what you wrote that you would like to say (in pseudo code):

If (TextBox1 and ComboBox1 have values) OR (AAX and AAY have values) AND all the rest of the boxes have values then return True, otherwise return False.

This code will get you there:

Function validate() As Boolean

    Dim ret As Boolean

    If (Len(TextBox1.Value) <> 0 And Len(ComboBox1.Value) <> 0) _
        Or (Len(AAX.Value) <> 0 And Len(AAY.Value) <> 0) _
        And Len(ComboBox2.Value) <> 0 And Len(ComboBox3.Value) <> 0 _
        And Len(RB.Value) <> 0 And Len(MFC.Value) = 0 _
        And Len(MCC.Value) <> 0 And Len(LB.Value) <> 0 _
        And Len(TB.Value) <> 0 And Len(BB.Value) <> 0 _
        And Len(ComboBox4.Value) <> 0 Then

        ret = True

    Else

        ret = False

    End If

    validate = ret

End Function

VBA function for checking empty combobox or textbox with complex condition

2 Answers2