VBA - variable gets ComboBox value else gets TextBox value error

Question

I am writing a VBA code that is opened from a UserForm, which includes 2 ComboBoxes and 2 TextBoxes to form a 2D matrix by user's choice.

What I'm trying to do is, have a variable get the combobox value (one variable for each combobox) and if the selection is null, then the variables get the value of the textboxes. I've googled the expression I need to use for that and stumbled upon IsNull, but the code doesn't work. What I tried was:

If IsNull(Matrix_Size_1.value) = False And IsNull(Matrix_Size_2.value) = False Then
    lWidth = Matrix_Size_1.value
    lHeight = Matrix_Size_2.value

ElseIf IsNull(Matrix_Size_1.value) = True And IsNull(Matrix_Size_2.value) = True Then
    lWidth = Matrix_Hand_1.value
    lHeight = Matrix_Hand_2.value
End If

Of course I also have to filter more plausible situations as: if one combobox is empty then msgbox, if everything is empty then msgbox but I'll handle it once I'll understand why my code doesn't work.

But the error I get if I DO NOT choose anything from the comboboxes is Type mismatch even though the If code has an option for both of the comboboxes to be empty.

I'd appreciate your help.

you might find it easier to use the ListIndex Property from the combobox control. If i recall, if ListIndex = -1 then the box has had no value selected. — Takarii

Siddharth Rout Siddharth Rout · Accepted Answer · 2016-08-02T08:06:53

If Comboboxes .Style property is set as fmStyleDropDownList then use this

If Matrix_Size_1.ListIndex = -1 And Matrix_Size_2.ListIndex = -1 Then

If Comboboxes .Style property is set as fmStyleDropDownCombo then use this

If Len(Trim(Matrix_Size_1.Value)) = 0 And Len(Trim(Matrix_Size_2.Value)) = 0 Then

You can check the Style property in the design mode by right clicking on the combobox and clicking on properties.

Edit

From the comments, I have understood that you actually want this

Dim lWidth  As Single, lHeight  As Single

If Len(Trim(Matrix_Size_1.Value)) = 0 And Len(Trim(Matrix_Size_2.Value)) = 0 Then
    lWidth = Val(Matrix_Hand_1.Value) '<~~> Val will take care of mismatch caused by empty boxes
    lHeight = Val(Matrix_Hand_2.Value)
Else
    lWidth = Val(Matrix_Size_1.Value)
    lHeight = Val(Matrix_Size_2.Value)
End If

VBA - variable gets ComboBox value else gets TextBox value error

1 Answers