Filtering a listbox from multiple entries in comboboxes

[JonnyAngelo]

Hello and a happy new year to everyone on this forum!

I am trying to filter a listbox full of data from one of the worksheets.
I have 8 combo-boxes that have options within them which i want to utilise
to filter the information already in the list box. I already have the listbox
working when i start up the userform.
There are 20 columns within the listbox data and 8 of them are
associated with the 8 combo-boxes.

Here is an image of the userform that i have created for this (apologies for the scribbles, had to hide some of the data shown):

I feel as if this is an easy code to fix up but i'm still learning VB and am unsure as how to do this,
Feel free to share your suggestions.

Thank you, Jonny.

 

[reberryjr]

I'm unable to see the pictures you uploaded (could be a firewall issue on my work laptop), so I have to ask...are you trying to get the values of the 8 columns into your listbox?

 

[MickG]

If your trying to load individual columns from the list box to the Comboboxes then Try something like this:-

With ComboBox1
   .List = Application.Index(ListBox1.List, Evaluate("Row(1:" & ListBox1.ListCount & " )"), 2) 'Change 2 to column require
End With

 

[JonnyAngelo]

Hello guys, sorry for being unclear,

I have values already in the combo-boxes that match data on the listbox.
e.g. for the DSS / Private combo-box, i can select either Private or DSS as an option.
Say i select private and click on the "Filter Data" command button, what i want to
happen is for the listbox to show rows of entries with the values of "Private" in one
of the columns.

 

[JonnyAngelo]

Here's some extra information. The code i'm currently using to fill up my list box is:

Private Sub UserForm_Initialize()


Application.ScreenUpdating = False
Worksheets("Landlord").Activate


    Dim lbtarget As MSForms.ListBox
    Dim sht As Worksheet
    Dim LastRow As Long
    Dim LastColumn As Long
    Dim StartCell As Range


Set sht = Worksheets("Landlord")
Set StartCell = Range("A3:T3")


'Find Last Row and Column
  LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
  LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column


'Select Range
  sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select
  
'Set reference to the range of data to be filled
    Set rngSource = Worksheets("Landlord").Range(StartCell, sht.Cells(LastRow, LastColumn))
     'Fill the listbox
    Set lbtarget = Me.lstData
    With lbtarget
         'Determine number of columns
        .ColumnCount = -1
         'Set column widths
        .ColumnWidths = "50;50;60;60;100;60;75;40;40;40;50;40;50;50;65;20;75;20;50;40;"
         'Insert the range of data supplied
        .List = rngSource.Cells.Value
 
Worksheets("HomePage").Activate


     End With

With the information collected from this code, i columns columns K - R as combo-boxes.
For example, column K is cbDSS and so if i select "Private" as one of the drop down option in the
cbDSS selections, i want to see the listbox populate with data that only has the value "Private" in column K.

If i were to select an option from the next combo-box (cbContract) and selected "1 year" for example, I would
then want the listbox to then populate further so that it populates with the values of "1 year" in column L
With column K still having the "Private" value only.

The same goes with the rest of the combo-boxes.

Column K = cbDSS
Column L = cbContract
Column M = cbType
Column N = cbIncl
Column O = cbFloor
Column P = cbMais
Column Q = cbKitch
Column R = cbGar

 

[MickG]

Try this in your userform Module:-

Should work with your data!!
NB:- Comboboxes names have been change , as per your Combobox Names List.
NB:- Alter other details as required

Option Explicit
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, Ray [COLOR="Navy"]As[/COLOR] Variant
Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
[COLOR="Navy"]Dim[/COLOR] cbBox [COLOR="Navy"]As[/COLOR] Variant, nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRay [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Rw [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
cbBox = Array(cbDSS, cbContract, cbType, cbIncl, cbFloor, cbMais, cbKitch, cbGar)
[COLOR="Navy"]For[/COLOR] n = 0 To UBound(cbBox)
    nStr = nStr & IIf(nStr = "", cbBox(n), "," & cbBox(n))
[COLOR="Navy"]Next[/COLOR] n
    [COLOR="Navy"]If[/COLOR] Dic.exists(nStr) [COLOR="Navy"]Then[/COLOR]
        ReDim nRay(1 To Dic.Count, 1 To 20)
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Rw [COLOR="Navy"]In[/COLOR] Split(Dic(nStr), ",")
            c = c + 1
            [COLOR="Navy"]For[/COLOR] Ac = 1 To 20
                nRay(c, Ac) = Ray(Val(Rw), Ac)
            [COLOR="Navy"]Next[/COLOR] Ac
        [COLOR="Navy"]Next[/COLOR] Rw
        [COLOR="Navy"]With[/COLOR] ListBox1
            .Clear
            .ColumnCount = 20
            .ColumnWidths = "20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20"
            .List = nRay
        [COLOR="Navy"]End[/COLOR] With
    [COLOR="Navy"]Else[/COLOR]
            MsgBox "Related Data not Found"
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] sRay [COLOR="Navy"]As[/COLOR] Variant, nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] RngSource [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] RngSource = Sheets("LandLord").Range("A3").CurrentRegion.Resize(, 20)
[COLOR="Navy"]With[/COLOR] ListBox1
    .ColumnCount = 20
    .ColumnWidths = "20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20,20"
    .List = RngSource.Value
[COLOR="Navy"]End[/COLOR] With
Ray = RngSource.Value
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray, 1)
nStr = Join(Application.Index(Ray, n, Array(11, 12, 13, 14, 15, 16, 17, 18)), ",")
    [COLOR="Navy"]If[/COLOR] Not Dic.exists(nStr) [COLOR="Navy"]Then[/COLOR]
        Dic.Add nStr, n
    [COLOR="Navy"]Else[/COLOR]
        Dic(nStr) = Dic(nStr) & "," & n
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[JonnyAngelo]

Hello Mick,

I've added your part of code onto the userform module but it comes up with an error once i click on the command button (cmdFilter):
"Compile error, sub or functioned not defined". It also highlights 'Ray' just before (Val(Rw), Ac) in this section of the code:

    If Dic.exists(nStr) Then
        ReDim nRay(1 To Dic.Count, 1 To 20)
        For Each Rw In Split(Dic(nStr), ",")
            c = c + 1
            For Ac = 1 To 20
                nRay(c, Ac) = Ray(Val(Rw), Ac)

Thanks, Jonny

 

[MickG]

Have a look at this example file, see what the problem might be .
https://app.box.com/s/a2ye1zhxbezbxnjjjpln6qz2lrozv8cx

 

[JonnyAngelo]

Not sure if this is supposed to happen, but whenever i select values in the combo-boxes from your file
and click on the command button, it only comes up with the message box.

As for the code, i pasted it in the same way you did and made changes with the listbox names and such,
however i'm still not able to find out what the problem under cmdFilter_Click() is.

Its probably something small but i'm very new to VBA, apologies for the inexperience.

 

[MickG]

You need to select a rows with data that matches all the combobox criteria.
If you select the first item in each of the comboboxes that should give you one line in the Listbox.
If you select "Private" in the first combobox and then select line 3 from all the other comboxes then that should give you 3 rows in the Listbox.

Can you send me a copy/Example of your file Using "Box.com" or similar

When the code fails and you place the cursor over "Val(Rw)" what do you see ????