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,
this line

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

should only exist once as it builds the string to split in to an array of filtered results

Project is growing - be helpful if you could place copy of your workbook with sum dummy data on a file sharing site like dropbox & place a link to it here

Dave

 

[JackDomino1]

hi,
this line

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

should only exist once as it builds the string to split in to an array of filtered results

Project is growing - be helpful if you could place copy of your workbook with sum dummy data on a file sharing site like dropbox & place a link to it here

Dave

Thank-you, makes sense. However, if I remove either one of these entries, one of the filters stops working. I'm sure I am missing something simple.

Ill work on uploading a link to the project.

 

[JackDomino1]

Test Database.xlsm

drive.google.com

Here is a link

 

[dmt32]

ok have it - will take a look asap

Dave

 

[dmt32]

Hi,

Not fully sure that I have interpreted what you are doing correctly but see if this update goes in right direction

Private Sub UserForm_Initialize()
    Dim s           As Integer
    Dim ws          As Worksheet
  
    s = 1
  
    Set ws = ThisWorkbook.Worksheets("Fruits")
  
    With ThisWorkbook.Sheets("List")
        While .Cells(s, 1) <> ""
            .Cells(s, 1).Value = StrConv(.Cells(s, 1).Value, vbProperCase)
            Me.ComboBox5.AddItem (.Cells(s, 1))
            s = s + 1
        Wend
    End With
  
    TableArr = ws.Range("A1").CurrentRegion.Value2
  
    Me.ComboBox6.List = Array("Negative", "Positive", "Zero", "All")
    Me.ComboBox7.List = Array("Greater than 1.5", "Less than 1.5", "Equal To 1.5")
End Sub

Sub MakeList()
    Dim ListItem    As String
    Dim i           As Long
   
    For i = 1 To UBound(TableArr, 1)
        'selected color
        If UCase(TableArr(i, 2)) = UCase(Me.ComboBox5.Text) Then
           
            'selected equation
            If Val(TableArr(i, 3)) < 0 And Me.ComboBox6.Text = "Negative" Or _
            Val(TableArr(i, 3)) > 0 And Me.ComboBox6.Text = "Positive" Or _
            Val(TableArr(i, 3)) = 0 And Me.ComboBox6.Text = "Zero" Or _
            Val(TableArr(i, 3)) <> 0 And Me.ComboBox6.Text = "All" Or _
            Val(TableArr(i, 3)) = 0 And Me.ComboBox6.Text = "All" Then
           
            'selected stock age
            If Val(TableArr(i, 4)) > 1.5 And Me.ComboBox7.Text = "Greater than 1.5" Or _
            Val(TableArr(i, 4)) < 1.5 And Me.ComboBox7.Text = "Less than 1.5" Or _
            Val(TableArr(i, 4)) = 1.5 And Me.ComboBox7.Text = "Equal To 1.5" Then
           
            ListItem = IIf(Len(ListItem) = 0, TableArr(i, 1), ListItem & "," & TableArr(i, 1))
           
        End If
    End If
End If

Next

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

Ensure that you replace both current codes with above.

Dave

 

[JackDomino1]

Hi,

Not fully sure that I have interpreted what you are doing correctly but see if this update goes in right direction

Private Sub UserForm_Initialize()
    Dim s           As Integer
    Dim ws          As Worksheet
 
    s = 1
 
    Set ws = ThisWorkbook.Worksheets("Fruits")
 
    With ThisWorkbook.Sheets("List")
        While .Cells(s, 1) <> ""
            .Cells(s, 1).Value = StrConv(.Cells(s, 1).Value, vbProperCase)
            Me.ComboBox5.AddItem (.Cells(s, 1))
            s = s + 1
        Wend
    End With
 
    TableArr = ws.Range("A1").CurrentRegion.Value2
 
    Me.ComboBox6.List = Array("Negative", "Positive", "Zero", "All")
    Me.ComboBox7.List = Array("Greater than 1.5", "Less than 1.5", "Equal To 1.5")
End Sub

Sub MakeList()
    Dim ListItem    As String
    Dim i           As Long
  
    For i = 1 To UBound(TableArr, 1)
        'selected color
        If UCase(TableArr(i, 2)) = UCase(Me.ComboBox5.Text) Then
          
            'selected equation
            If Val(TableArr(i, 3)) < 0 And Me.ComboBox6.Text = "Negative" Or _
            Val(TableArr(i, 3)) > 0 And Me.ComboBox6.Text = "Positive" Or _
            Val(TableArr(i, 3)) = 0 And Me.ComboBox6.Text = "Zero" Or _
            Val(TableArr(i, 3)) <> 0 And Me.ComboBox6.Text = "All" Or _
            Val(TableArr(i, 3)) = 0 And Me.ComboBox6.Text = "All" Then
          
            'selected stock age
            If Val(TableArr(i, 4)) > 1.5 And Me.ComboBox7.Text = "Greater than 1.5" Or _
            Val(TableArr(i, 4)) < 1.5 And Me.ComboBox7.Text = "Less than 1.5" Or _
            Val(TableArr(i, 4)) = 1.5 And Me.ComboBox7.Text = "Equal To 1.5" Then
          
            ListItem = IIf(Len(ListItem) = 0, TableArr(i, 1), ListItem & "," & TableArr(i, 1))
          
        End If
    End If
End If

Next

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

Ensure that you replace both current codes with above.

Dave

Hi,

Sorry for the delayed reply. For some reason I didn't get an email to say you had replied.

Anyway, thanks for the code. Its almost there. The double filter system now works, but only filters if both CB6 and CB7 are in use.

I added one extra if, so that CB6 will filter first. Then this is refined further IF CB7 is updated.


Sub MakeList()

Dim ListItem As String
Dim i As Long

For i = 1 To UBound(TableArr, 1)
'selected color
If UCase(TableArr(i, 2)) = UCase(Me.ComboBox5.Text) Then

'selected equation
If Val(TableArr(i, 3)) < 0 And Me.ComboBox6.Text = "Negative" Or _
Val(TableArr(i, 3)) > 0 And Me.ComboBox6.Text = "Positive" Or _
Val(TableArr(i, 3)) = 0 And Me.ComboBox6.Text = "Zero" Or _
Val(TableArr(i, 3)) <> 0 And Me.ComboBox6.Text = "All" Or _
Val(TableArr(i, 3)) = 0 And Me.ComboBox6.Text = "All" Then

If ComboBox7.Value = "" Then
ListItem = IIf(Len(ListItem) = 0, TableArr(i, 1), ListItem & "," & TableArr(i, 1))

Else:

'selected stock age
If Val(TableArr(i, 4)) > 1.5 And Me.ComboBox7.Text = "Greater than 1.5" Or _
Val(TableArr(i, 4)) < 1.5 And Me.ComboBox7.Text = "Less than 1.5" Or _
Val(TableArr(i, 4)) = 1.5 And Me.ComboBox7.Text = "Equal To 1.5" Then

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

End If


End If
End If
End If

Next

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

This appears to be working well.

Thanks again for your time, much appreciated.
Jack

 

[dmt32]

welcome - glad resolved

Dave