Using comboboxes to filter a listbox

W

Waimea

Active Member
Joined
Jun 30, 2018
Messages
465
Office Version
  1. 365
Platform
  1. Windows
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.

Code: 
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?
 
Sort by date Sort by votes
I've just seen you previous Post #3 .

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
Click to expand...

Where did the "Textboxes" come from, and what are they for, up to now we've only had Comboboxes.
How do you propose to use then for values ????
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi Mick, I am sorry for not being clear!

There are no textboxes! There are six comboboxes, I want two of them to contain text, combobox1 and combobox2

Ex.

Code: 
With Me.ComboBox1
.AddItem "Europe"
.AddItem "Asia"
.AddItem "Africa"
End With


With Me.ComboBox2
.AddItem "Sweden"
.AddItem "Norway"
End With

I have a select case for combobox1 so that combobox2 is dependent on combobox1, not sure if this matters?
 
Last edited:
Upvote 0
I'm rather confused now !!!.
Would it be better if you showed an example of your data and described how you wanted all these 6 comboboxes to work.
And perhaps the basic code, including the select case code your using.
 
Upvote 0
Hi Mick,

thank you for your reply! I will link a picture of what I am trying to do.

https://ibb.co/yQwRTRR

In the picture if I select Asia in the first combobox I want to show all entries with Asia.

If I select Country1 in the second combobox I want all metrics for that country to show.

If I select >50 in combobox3 I want all metrics3 that are above 50 to show.

Etc.


I think that I want combobox 1 and combobox 2 to be optional, and if I select only one metric the rest of the metrics are also optional?

Is this clearer?

yQwRTRR
yQwRTRR
Code: 
Private Sub ComboBox1_Change()


Me.ComboBox2 = ""
Select Case Me.ComboBox1


    Case "Asia"
        With Me.ComboBox2
        .AddItem "Country1"
        .AddItem "Country2"
        End With
    
    Case "Europe"
        With Me.ComboBox2
        .AddItem "Country3"
        .AddItem "Country4"
        End With
        
End Select
    
End Sub






Private Sub UserForm_Initialize()


Myarray = Sheet3.ListObjects("Table1").DataBodyRange.Value


With ListBox1
    .ColumnCount = 6
    .List = Myarray
    .ColumnWidths = "70;70;70;70;70"
End With


With ComboBox1
    .AddItem "Asia"
    .AddItem "Europe"
End With


'With ComboBox2
'    .AddItem ""
'    .AddItem ""
'End With


With Me.ComboBox3
.AddItem ">0"
.AddItem ">50"
.AddItem ">500"
.AddItem ">5000"
.AddItem ">50000"
End With


With Me.ComboBox4
.AddItem ">0"
.AddItem ">50"
.AddItem ">500"
.AddItem ">5000"
.AddItem ">50000"
End With


With Me.ComboBox5
.AddItem ">0"
.AddItem ">50"
.AddItem ">500"
.AddItem ">5000"
.AddItem ">50000"
End With


With Me.ComboBox6
.AddItem ">0"
.AddItem ">50"
.AddItem ">500"
.AddItem ">5000"
.AddItem ">50000"
End With


End Sub
 
Last edited:
Upvote 0
Does this mean if you select just one combobox that is the criteria , but if you select all six then all 6 criteria should apply.
So any number of comboboxes that are selected would form the criteria you want.

I think that I want combobox 1 and combobox 2 to be optional, and if I select only one metric the rest of the metrics are also optional
Click to expand...
 
Upvote 0
Hi Mick,

thank you for your reply!

Yes, that sounds like a fantastic suggestion from you!
 
Upvote 0
Try this for the 6 Comboboxes.

Code: 
Option Explicit
[COLOR="Navy"]Dim[/COLOR] Myarray [COLOR="Navy"]As[/COLOR] Variant
Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
Myarray = Sheet3.ListObjects("Table2").DataBodyRange.Value

[COLOR="Navy"]With[/COLOR] ListBox1
    .ColumnCount = 6
    .List = Myarray
    .ColumnWidths = "70;70;70;70;70;70"
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]With[/COLOR] ComboBox1
    .AddItem ""
    .AddItem "Asia"
    .AddItem "Europe"
    .AddItem "America"
[COLOR="Navy"]End[/COLOR] With


'[COLOR="Green"][B]With ComboBox2[/B][/COLOR]
'[COLOR="Green"][B]    .AddItem ""[/B][/COLOR]
'[COLOR="Green"][B]    .AddItem ""[/B][/COLOR]
'[COLOR="Green"][B]End With[/B][/COLOR]


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


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


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


[COLOR="Navy"]With[/COLOR] Me.ComboBox6
.AddItem ""
.AddItem ">0"
.AddItem ">50"
.AddItem ">500"
.AddItem ">5000"
.AddItem ">50000"
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()
Me.ComboBox2 = ""
[COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Me.ComboBox1
     [COLOR="Navy"]Case[/COLOR] "Asia"
        [COLOR="Navy"]With[/COLOR] Me.ComboBox2
           .AddItem ""
           .AddItem "Country1"
           .AddItem "Country2"
        [COLOR="Navy"]End[/COLOR] With
    
    [COLOR="Navy"]Case[/COLOR] "Europe"
        [COLOR="Navy"]With[/COLOR] Me.ComboBox2
            .AddItem ""
            .AddItem "Country3"
            .AddItem "Country4"
        [COLOR="Navy"]End[/COLOR] With
     
     [COLOR="Navy"]Case[/COLOR] "America"
        [COLOR="Navy"]With[/COLOR] Me.ComboBox2
            .AddItem ""
            .AddItem "Texas"
            .AddItem "Ohio"
        [COLOR="Navy"]End[/COLOR] With


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

Private [COLOR="Navy"]Sub[/COLOR] ComboBox2_Change()
Update
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] ComboBox3_Change()
Update

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] ComboBox4_Change()
Update
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] ComboBox5_Change()
Update
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] ComboBox6_Change()
Update
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Sub[/COLOR] Update()
[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] Sn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Fd [COLOR="Navy"]As[/COLOR] Boolean, nn [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)
 Fd = False
 [COLOR="Navy"]For[/COLOR] ac = 1 To 6
       [COLOR="Navy"]If[/COLOR] ac < 3 [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]If[/COLOR] Me.Controls("Combobox" & ac).Object.Value = "" Or Myarray(n, ac) _
            = Me.Controls("Combobox" & ac).Object.Value [COLOR="Navy"]Then[/COLOR]
                Fd = True
            [COLOR="Navy"]Else[/COLOR]
                Fd = False
                [COLOR="Navy"]Exit[/COLOR] For
            [COLOR="Navy"]End[/COLOR] If
      [COLOR="Navy"]ElseIf[/COLOR] ac > 2 [COLOR="Navy"]Then[/COLOR]
           [COLOR="Navy"]If[/COLOR] Me.Controls("Combobox" & ac).Object.Value = "" Or Myarray(n, ac) _
           > Val(Mid(Me.Controls("Combobox" & ac).Object.Value, 2)) [COLOR="Navy"]Then[/COLOR]
            Fd = True
         [COLOR="Navy"]Else[/COLOR]
            Fd = False
            [COLOR="Navy"]Exit[/COLOR] For
        [COLOR="Navy"]End[/COLOR] If
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] ac
  [COLOR="Navy"]If[/COLOR] Fd [COLOR="Navy"]Then[/COLOR]
      c = c + 1
        [COLOR="Navy"]For[/COLOR] nn = 1 To 6
            Ray(c, nn) = Myarray(n, nn)
       [COLOR="Navy"]Next[/COLOR] nn
  [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
 
Upvote 0
Hi Mick,

this is flawless!

I have one question, would it be possible to remove the dependent combobox2 and use .AddItem or .List instead for combobox2?
 
Upvote 0
No problem .
Add the code below to the "UserForm_Initialize" code and remove the Select case from the Combobox1_change code.
Don't forget to leave the calling line "Update" at the bottom of the code.

NB:- In each combobox in this new code I have a added Blank row (i.e. "") so you can select a empty combobox value


Something like:-
Code: 
 With Me.ComboBox2
    .AddItem ""
    .AddItem "Country1"
    .AddItem "Country2"
    .AddItem "Country3"
    .AddItem "Country4"
    .AddItem "Texas"
    .AddItem "Ohio"
End With
 
Upvote 0
Hi Mick,

thank you for your time and your help!

It is very kind of you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
sum or subtraction based on selected item from combobox on userform
Replies
3
Views
202
Mussa
M
J
  • Question Question
Filter ListBox on a UserForm using a ComboBox
Replies
1
Views
296
DanteAmor
DanteAmor
T
  • Question Question
Filter data in listbox based on three conditions by selected from combobox
Replies
1
Views
460
tubrak
T
R
  • Solved
finding last row issue
Replies
3
Views
232
Fluff
Fluff
P
  • Question Question
Copy data from textbox and Combobox into Listbox
Replies
1
Views
382
Patriot2879
P

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top