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
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,480
Office Version
  1. 2019
Platform
  1. Windows
hi,
this line

VBA Code:
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

JackDomino1

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

VBA Code:
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.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,480
Office Version
  1. 2019
Platform
  1. Windows
ok have it - will take a look asap

Dave
 

dmt32

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

ADVERTISEMENT

Hi,

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


VBA Code:
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
 
Last edited:

JackDomino1

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

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


VBA Code:
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,480
Office Version
  1. 2019
Platform
  1. Windows
welcome - glad resolved

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,127,422
Messages
5,624,701
Members
416,042
Latest member
Oden

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