Using comboboxes to filter a listbox

[Waimea]

I have an userform with 6 comboboxes, named combobox1, combobox2, combobox3, combobox4, combobox5, combobox6 and a listbox named listbox1.

I have a textbox named textbox1 and a button named commandbutton1.

I want to filter the listbox with criteria in the comboboxes such as "> 200" or "< 1000."

So if I select ">200" in combobox1 and ">5000" in combobox2 the items where this is true would populate the listbox.

Private Sub CommandButton1_Click()
    Dim Crit1 As String
    Dim Crit2 As String
    Dim Crit3 As String
    Dim Crit4 As String
    Dim Crit5 As String
    Dim Crit6 As String
    
    Crit1 = ComboBox1.Value
    Crit2 = ComboBox2.Value
    Crit3 = ComboBox3.Value
    Crit4 = ComboBox4.Value
    Crit5 = ComboBox5.Value
    Crit6 = ComboBox6.Value

End Sub

I have searched and found this thread https://www.mrexcel.com/forum/excel...utofilter-2.html?highlight=listbox+search+vba However the code doesn't do what I am trying to do.

Do I need to use autofilter? Where do I start?

 

[Waimea]

Private Sub UserForm_Initialize()

Dim myArray As Variant
myArray = Sheet1.ListObjects("Table1").DataBodyRange.Value


ListBox1.List = MyArray
ListBox1.ColumnCount = 6

With ComboBox1
.AddItem ">0
.AddItem ">500"
.AddItem ">5000"
.AddItem ">50000"
.AddItem ">500000"
End With

I know how to load an array into listbox1, how to add items to comboboxes but not how to filter a listbox or how to filter the array? The examples I have searched for changes the Table and not the array/listbox.

It would be very kind of you to help me, I need some hints on how to proceed?

 

[dmt32]

Hi,
have a look here:https://www.ozgrid.com/VBA/listbox-filter.htm

you can download workbook which may be of help to you

Dave

 

[Waimea]

Hi dmt2,

your link is great and it is what I am looking for.

Thank you!

 

[Waimea]

Hi again, I don't fully understand the code in the link but I will try to adapt it.

If I want to filter on combox1 > 500 and combobox2 > 500 I think that I only need the AND part?

When would the OR make sense?

If combobox1 > 500 OR combobox2 > 5000?

 

[MickG]

Try this, paste Both codes in Userform Module, starting at top !!:-
(Mod as required)
NB:- The Numbers (Criteria) in code are based on the first column in array

Option Explicit
Dim Myarray As Variant

Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
Myarray = Sheet2.ListObjects("Table1").DataBodyRange.Value

ListBox1.List = Myarray
ListBox1.ColumnCount = 6

[COLOR="Navy"]With[/COLOR] Me.ComboBox1
.AddItem ">0"
.AddItem ">5"
.AddItem ">50"
.AddItem ">500"
.AddItem ">5000"
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]With[/COLOR] Me.ComboBox2
.AddItem "<0"
.AddItem "<5"
.AddItem "<50"
.AddItem "<500"
.AddItem "<5000"
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private Sub CommandButton1_Click()
Dim n As Long, c As Long, ac As Long
ReDim Ray(1 To UBound(Myarray, 1), 1 To UBound(Myarray, 2))
For n = 1 To UBound(Myarray)
    If Myarray(n, 1) >= Val(Mid(ComboBox1.Value, 2)) And Myarray(n, 1) <= Val(Mid(ComboBox2.Value, 2)) Then
        c = c + 1
        For ac = 1 To 6
            Ray(c, ac) = Myarray(n, ac)
       Next ac
    End If
Next n
With ListBox1
    .Clear
    .List = Ray
End With
End Sub

Regards Mick

 

[Waimea]

Hi Mick,

thank you for your reply and your code. I got your code working but I have two questions.

1. How do I add a text filter?

If myArray(n, 1) >= Val(Mid(ComboBox1.Value, 2)) And myArray(n, 1) >= Val(Mid(ComboBox2.Value, 2)) And myArray(n, 1) >= Val(Mid(ComboBox3.Value, 2)) Or myArray(n, 1) = Val(Mid(ComboBox4.Value, 2)) Then

With Me.ComboBox4
.AddItem "Yes"
.AddItem "No"
End With

2. What event should I use for the change in any of the comboboxes to trigger a update off the listbox?

Ex. I select Yes in combobox4 and then all items with "Yes" in them populates the listbox (regardless of the other comboboxes).

 

[Waimea]

I haven't really though out what I am trying to do.

I want to be able to sort on text in the two first comboboxes and on values in the four remaining textboxes.

I think your code does exactly what I am trying to do.

 

[MickG]

Try this:-
The Code now runs when a Combobox.value is changed
The Extra Combobox is Combobox3, and I have added an extra item "Numeric"
To Use the numeric criteria in the code, "Numeric" must be first selected in combobox3, Else select "Yes" or "No".

Option Explicit
[COLOR="Navy"]Dim[/COLOR] Myarray [COLOR="Navy"]As[/COLOR] Variant


Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
Myarray = Sheet2.ListObjects("Table1").DataBodyRange.Value

ListBox1.List = Myarray
ListBox1.ColumnCount = 6

[COLOR="Navy"]With[/COLOR] Me.ComboBox1
.AddItem ">0"
.AddItem ">5"
.AddItem ">50"
.AddItem ">500"
.AddItem ">5000"
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]With[/COLOR] Me.ComboBox2
.AddItem "<0"
.AddItem "<5"
.AddItem "<50"
.AddItem "<500"
.AddItem "<5000"
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]With[/COLOR] Me.ComboBox3
.AddItem "Yes"
.AddItem "No"
.AddItem "Numeric"
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]



Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()
Update ComboBox1, ComboBox2, ComboBox3
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]



Private [COLOR="Navy"]Sub[/COLOR] ComboBox2_Change()
Update ComboBox1, ComboBox2, ComboBox3
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]



Private [COLOR="Navy"]Sub[/COLOR] ComboBox3_Change()
Update ComboBox1, ComboBox2, ComboBox3

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]




[COLOR="Navy"]Sub[/COLOR] Update(c1 [COLOR="Navy"]As[/COLOR] Object, c2 [COLOR="Navy"]As[/COLOR] Object, c3 [COLOR="Navy"]As[/COLOR] Object)
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
ReDim Ray(1 To UBound(Myarray, 1), 1 To UBound(Myarray, 2))
[COLOR="Navy"]For[/COLOR] n = 1 To UBound(Myarray)
  [h1] = c3.Value
  [COLOR="Navy"]If[/COLOR] c3.Value = "Numeric" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Myarray(n, 1) >= Val(Mid(c1.Value, 2)) And Myarray(n, 1) <= Val(Mid(c2.Value, 2)) [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        [COLOR="Navy"]For[/COLOR] ac = 1 To 6
            Ray(c, ac) = Myarray(n, ac)
       [COLOR="Navy"]Next[/COLOR] ac
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]ElseIf[/COLOR] Not c3.Value = "Numerica" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Myarray(n, 1) = c3.Value [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        [COLOR="Navy"]For[/COLOR] ac = 1 To 6
            Ray(c, ac) = Myarray(n, ac)
       [COLOR="Navy"]Next[/COLOR] ac
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] ListBox1
    .Clear
    .List = Ray
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Waimea]

Hi Mick,

thank you again for your reply. I am not sure on how to use Numeric?

It would be very kind of you if you could explain this part of the code in words?

 c = c + 1
        For ac = 1 To 6
            Ray(c, ac) = Myarray(n, ac)
       Next ac

And the use of

Val(Mid(c1.value,2) )

?