Filtering data into a userform listbox

[JonnyAngelo]

Hi!

Apologies for a bit of a nooby thread, I'm super new to VBA.
Simply put, I have created this userform and want it to show data within a listbox in the userform.
The data I have is stored on a worksheet and I want the listbox to also list new data once added to
the worksheet. The filtered options i have are from 8 combo boxes that I have added to the userform.
I also have a filter button which I would like to use once filling in the combo boxes to show the
filtered data on the listbox.

Here's an image of my userform so that you have an idea:

https://imgur.com/a/FOcUh

I've tried searching through methods such as Advanced Filtering and such but couldn't manage to get
ways to work with it. Any help is immensely appreciated.

Thanks, Jonny

 

[Vinoth_Shob]

Goto Properties of the Combobox and fill the column range where you have the data in "List Fill Range"

 

[JonnyAngelo]

Hi Vinoth,
Could you please explain what this does?
Could you also give me a step by step guide?

Apologies for being a pain,
Jonny

 

[JonnyAngelo]

Quick Update:

I've got a code that is close to working. The only current problems showing are:
- When matches are found, the List shows as blank
- I'm also trying to fix this error:
'Run-time error 308: Could not set the List property. Invalid property value'

Here is the code I'm running:

Private Sub cmdFilter_Click()    
    
    Dim rngToSearch As Range
    Dim rngToFind As Range
    Dim valToFind As Variant
    Dim arrClearList()
    
    valToFind = cbDSS.Value
    valToFind = cbContract.Value
    valToFind = cbType.Value
    valToFind = cbIncl.Value
    valToFind = cbFloor.Value
    valToFind = cbMais.Value
    valToFind = cbKitch.Value
    valToFind = cbGar.Value
    
    With Worksheets("Landlord")
        Set rngToSearch = .Columns("A:T")
    End With
    Set rngToFind = rngToSearch.Find(What:=valToFind, _
            LookIn:=xlFormulas, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)
            
    If Not rngToFind Is Nothing Then
        
        'Call ClearList(Me.ListBox1)
        
        lstData.AddItem
        
        With lstData
            .List(.ListCount - 1, 0) = rngToFind.Value  'Date Col A
            .List(.ListCount - 1, 1) = rngToFind.Offset(0, 1).Value 'Reference Col B
            .List(.ListCount - 1, 2) = rngToFind.Offset(0, 3).Value 'Name Col C
            .List(.ListCount - 1, 3) = rngToFind.Offset(0, 4).Value 'Contact Number Col D
            .List(.ListCount - 1, 4) = rngToFind.Offset(0, 5).Value 'Email E
            .List(.ListCount - 1, 5) = rngToFind.Offset(0, 6).Value 'Location F
            .List(.ListCount - 1, 6) = rngToFind.Offset(0, 7).Value 'Address G
            .List(.ListCount - 1, 7) = rngToFind.Offset(0, 8).Value 'Postcode H
            .List(.ListCount - 1, 8) = rngToFind.Offset(0, 9).Value 'Rent I
            .List(.ListCount - 1, 9) = rngToFind.Offset(0, 10).Value 'Commission J
            .List(.ListCount - 1, 10) = rngToFind.Offset(0, 11).Value 'DSS K
            .List(.ListCount - 1, 11) = rngToFind.Offset(0, 12).Value 'Contract L
            .List(.ListCount - 1, 12) = rngToFind.Offset(0, 13).Value 'Property M
            .List(.ListCount - 1, 13) = rngToFind.Offset(0, 14).Value 'Inclusive N
            .List(.ListCount - 1, 14) = rngToFind.Offset(0, 15).Value 'Floor O
            .List(.ListCount - 1, 15) = rngToFind.Offset(0, 16).Value 'Maisonette P
            .List(.ListCount - 1, 16) = rngToFind.Offset(0, 17).Value 'Kitch Q
            .List(.ListCount - 1, 17) = rngToFind.Offset(0, 18).Value 'Garden R
            .List(.ListCount - 1, 18) = rngToFind.Offset(0, 19).Value 'Date Av S
            .List(.ListCount - 1, 19) = rngToFind.Offset(0, 20).Value 'Viewing T
        
        End With

    Else
        MsgBox valToFind & " not found in worksheet."
    End If
            
End Sub

 

[My Aswer Is This]

It interesting you say your brand new to Vba but you have managed to put together all this code.

I think it would be best to tell us in detail what your attempting to do and then we may be able to help you.

 

[JonnyAngelo]

Haha, well in all honestly, I've been working on VBA for about 4 days in total.

As for what i'm trying to do with my userform, i want my listbox to show the data from one of my worksheets(Landlord).
The 8 combo boxes that i have added are for the purpose of filtering after the data has been brought up.
In a sense, after i click on the filter button, I want a list to show up with all of the data that matches with the options i have selected
from the combo boxes.

 

[My Aswer Is This]

Lets go at this one step at a time:
You said:
"As for what i'm trying to do with my userform, i want my listbox to show the data from one of my worksheets(Landlord).

What is the name of the listbox?

Where on the sheet named Lanlord might the script find the values to load into this listbox.


Please always provide specific details like this.

And I never click on links so don't say look at my link.
And you want to do a filter on 8 different columns? At one time

So if column 2 =Me and column 3=You and column 4="Car" etc. etc.
And you going to select one value from each listbox.

 

[JonnyAngelo]

Just managed to figure out how to add images, Here:

- The listbox name is currently 'lstData'
- The spreadsheet has 20 columns (A-T), the data used to find matches are in columns K through to R.

K = DSS / Private
L = Contract Term
M = Property Type
N = Inclusive
O = Floor
P = Maisonette
Q = Kitch
R = Garden

These are what i want to use as 'Filters' per say. However, i want the whole rows of data from worksheet(Landlord) to show up (Not just from K - R).

 

[My Aswer Is This]

OK. I will work on this.

 

[JonnyAngelo]

Thank you. If you require any more information, feel free to ask.