Listbox to populated based on two (unlinked) combo boxes

[JackDomino1]

Hi I have a table of data I wish to "filter" using two comboboxes and display the matching results in a listbox.

Colour	Item	Stock
Red	Apple	5​
Yellow	Peach	13​
Green	Pear	1​
Red	Cherry	0​
Red	Raspberry	-10​
Yellow	Plum	2​
Yellow	Apricot	0​
Red	Strawberry	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

 

[dmt32]

Hi,

Try following & see if does what you want

Place all codes in your userforms code page

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]

When you adding new rows is there be a new color and new Item names?

 

[JackDomino1]

Hi,

Try following & see if does what you want

Place all codes in your userforms code page

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]

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

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

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

Dave

 

[JackDomino1]

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

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

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]

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]

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]

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]

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