Listbox to populated based on two (unlinked) combo boxes

JackDomino1

New Member
Joined
Apr 7, 2020
Messages
39
Office Version
  1. 365
Platform
  1. Windows
Hi I have a table of data I wish to "filter" using two comboboxes and display the matching results in a listbox.


ColourItemStock
RedApple
5​
YellowPeach
13​
GreenPear
1​
RedCherry
0​
RedRaspberry
-10​
YellowPlum
2​
YellowApricot
0​
RedStrawberry
3​

Combobox1 should list available colours (done)
Combobox2 should list "Positive", "Zero" and "Negative"

If the user selects "red" and "positive", the listbox should display "Strawberry" and "apple" on separate rows.

I have managed to make this work, but only based on the colour selection, adding in the secondary "filter" has complicated things. Also, not sure if it is important, the rows in the table are constantly being added too.

Any pointers is greatly appreciated.
Thanks
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,512
Office Version
  1. 2019
Platform
  1. Windows
Hi,

Try following & see if does what you want

Place all codes in your userforms code page


Rich (BB code):
Dim TableArr As Variant

Private Sub ComboBox1_Change()
    MakeList
End Sub

Private Sub ComboBox2_Change()
    MakeList
End Sub

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    TableArr = ws.Range("A1").CurrentRegion.Value2
    
    Me.ComboBox2.List = Array("Negative", "Positive", "Zero")
End Sub

 Sub MakeList()
    Dim ListItem As String
    Dim i As Long
    For i = 1 To UBound(TableArr, 1)
        If TableArr(i, 1) = Me.ComboBox1.Text Then
            With Me.ComboBox2
                If Val(TableArr(i, 3)) < 0 And .Text = "Negative" Or _
                    Val(TableArr(i, 3)) > 0 And .Text = "Positive" Or _
                    Val(TableArr(i, 3)) = 0 And .Text = "Zero" Then
                        ListItem = IIf(Len(ListItem) = 0, TableArr(i, 2), ListItem & "," & TableArr(i, 2))
                End If
            End With
        End If
    Next
        Me.ListBox1.List = CVar(Split(ListItem, ","))
End Sub




Note the variable TableArr – this MUST sit at very TOP of your forms code page OUTSIDE any procedure.

I have assumed:
  • table starts in Range A1
  • worksheet is name Sheet1
  • worksheet is not protected
  • ComboBox1 populated by your code
Hope Helpful



Dave
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
When you adding new rows is there be a new color and new Item names?
 

JackDomino1

New Member
Joined
Apr 7, 2020
Messages
39
Office Version
  1. 365
Platform
  1. Windows
Hi,

Try following & see if does what you want

Place all codes in your userforms code page


Rich (BB code):
Dim TableArr As Variant

Private Sub ComboBox1_Change()
    MakeList
End Sub

Private Sub ComboBox2_Change()
    MakeList
End Sub

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
   
    TableArr = ws.Range("A1").CurrentRegion.Value2
   
    Me.ComboBox2.List = Array("Negative", "Positive", "Zero")
End Sub

Sub MakeList()
    Dim ListItem As String
    Dim i As Long
    For i = 1 To UBound(TableArr, 1)
        If TableArr(i, 1) = Me.ComboBox1.Text Then
            With Me.ComboBox2
                If Val(TableArr(i, 3)) < 0 And .Text = "Negative" Or _
                    Val(TableArr(i, 3)) > 0 And .Text = "Positive" Or _
                    Val(TableArr(i, 3)) = 0 And .Text = "Zero" Then
                        ListItem = IIf(Len(ListItem) = 0, TableArr(i, 2), ListItem & "," & TableArr(i, 2))
                End If
            End With
        End If
    Next
        Me.ListBox1.List = CVar(Split(ListItem, ","))
End Sub




Note the variable TableArr – this MUST sit at very TOP of your forms code page OUTSIDE any procedure.

I have assumed:
  • table starts in Range A1
  • worksheet is name Sheet1
  • worksheet is not protected
  • ComboBox1 populated by your code
Hope Helpful



Dave
Works perfectly, thank you!

I am a little unfamiliar with how Arrays work. Every time I think I understand, I don't.
My next task is assigning a fourth option to CB2, "Unknown". If this is selected, all results are shown, regardless if they are negative, positive or zero.

Thanks again for your help
Jack
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,512
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Works perfectly, thank you!

I am a little unfamiliar with how Arrays work. Every time I think I understand, I don't.
My next task is assigning a fourth option to CB2, "Unknown". If this is selected, all results are shown, regardless if they are negative, positive or zero.

Thanks again for your help
Jack
Hi,
glad suggestion helps

Reading a range in to an array is generally much faster & easier to manage in code.

You can try adding another Or statement to Include Unknown test & see if does what you want

Rich (BB code):
If Val(TableArr(i, 3)) < 0 And .Text = "Negative" Or _
                   Val(TableArr(i, 3)) > 0 And .Text = "Positive" Or _
                   Val(TableArr(i, 3)) = 0 And .Text = "Zero" Or _
                   .Text = "Unknown" The

Update ComboBox list

VBA Code:
Me.ComboBox2.List = Array("Negative", "Positive", "Zero", "Unknown")

Dave
 

JackDomino1

New Member
Joined
Apr 7, 2020
Messages
39
Office Version
  1. 365
Platform
  1. Windows
Hi,
glad suggestion helps

Reading a range in to an array is generally much faster & easier to manage in code.

You can try adding another Or statement to Include Unknown test & see if does what you want

Rich (BB code):
If Val(TableArr(i, 3)) < 0 And .Text = "Negative" Or _
                   Val(TableArr(i, 3)) > 0 And .Text = "Positive" Or _
                   Val(TableArr(i, 3)) = 0 And .Text = "Zero" Or _
                   .Text = "Unknown" The

Update ComboBox list

VBA Code:
Me.ComboBox2.List = Array("Negative", "Positive", "Zero", "Unknown")

Dave
Sorry for the delayed reply.


This is exactly how I tried to tackle the addition of "Unknown". Working well. Thanks again
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,512
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Sorry for the delayed reply.


This is exactly how I tried to tackle the addition of "Unknown". Working well. Thanks again

most welcome

Dave
 

JackDomino1

New Member
Joined
Apr 7, 2020
Messages
39
Office Version
  1. 365
Platform
  1. Windows
most welcome

Dave
I wonder, how tricky would it be to add a third combobox?

I am imagining the listbox populates as it does now, after the original two comboboxes are filled. However, is it possible to have the option to refine the listbox list further with a third, or maybe even a fourth combobox?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,512
Office Version
  1. 2019
Platform
  1. Windows
I wonder, how tricky would it be to add a third combobox?

I am imagining the listbox populates as it does now, after the original two comboboxes are filled. However, is it possible to have the option to refine the listbox list further with a third, or maybe even a fourth combobox?

The array is populated by the Range.CurrentRegion property (all your data) so you can test any combination of array elements needed to build the filter.

MakeList is a common code that you would call from each of your comboboxes & where you can add other tests as required. However, if filtering is quite complex there may be alternative solution(s) to consider.

Suggest just have a go & make changes & see how you you get on - you can post back if need further help.

Dave
 

JackDomino1

New Member
Joined
Apr 7, 2020
Messages
39
Office Version
  1. 365
Platform
  1. Windows
The array is populated by the Range.CurrentRegion property (all your data) so you can test any combination of array elements needed to build the filter.

MakeList is a common code that you would call from each of your comboboxes & where you can add other tests as required. However, if filtering is quite complex there may be alternative solution(s) to consider.

Suggest just have a go & make changes & see how you you get on - you can post back if need further help.

Dave
Thank you, I think I have managed to get half way there.

The second filter now sort-of works. But not quite. The listbox is populating as we commanded it too previously. However, the second filter is adding too the list, not updating it. Any which did not meet the first criteria, but do meet the second, are added. Any which meet both, are duplicated.

I'm sure I'm missing something obvious,


Sub MakeList()
Dim ListItem As String
Dim i As Long

For i = 1 To UBound(TableArr, 1)
If TableArr(i, 2) = Me.ComboBox5.Text Then
With Me.ComboBox6
If Val(TableArr(i, 13)) < 0 And .Text = "Negative" Or _
Val(TableArr(i, 13)) > 0 And .Text = "Positive" Or _
Val(TableArr(i, 13)) = 0 And .Text = "Zero" Or _
Val(TableArr(i, 13)) <> 0 And .Text = "All" Or _
Val(TableArr(i, 13)) = 0 And .Text = "All" Then

ListItem = IIf(Len(ListItem) = 0, TableArr(i, 1), ListItem & "," & TableArr(i, 1))
End If

End With

With Me.ComboBox7
If Val(TableArr(i, 10)) > 1.5 And .Text = "Greater than 1.5" Or _
Val(TableArr(i, 10)) < 1.5 And .Text = "Less than 1.5" Or _
Val(TableArr(i, 10)) = 1.5 And .Text = "Equal to 1.5" Then
ListItem = IIf(Len(ListItem) = 0, TableArr(i, 1), ListItem & "," & TableArr(i, 1))
End If

End With
End If


Next
Me.ListBox1.List = CVar(Split(ListItem, ","))

End Sub

Ill continue to tinker with it, but any help is appreciated.
Jack
 

Watch MrExcel Video

Forum statistics

Threads
1,129,538
Messages
5,636,901
Members
416,948
Latest member
Jkpang

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
Top