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

 

[MrIfOnly]

This is just setting up variables based on the settings of the various comboboxes and a pair of radio buttons (units) that the user uses to narrow his search in the SearchSettings userform. The variables will be used to apply the autofilter to search for the various criteria only if the list index of a particular combobox is greater than 0 (meaning something has been selected in that combobox). See the code that follows:

    'No Search Criteria Selected    If ctypeli < 1 And custli < 1 And daterli < 1 And sizeli < 1 And size2li < 1 And mtrlli < 1 And tmtrlli < 1 Then
        Application.Cursor = xlDefault
        response = MsgBox("Please select at least one search criterion.", _
        vbExclamation + vbOKOnly, "No Search Criteria Selected.")
        SearchSettings.StatusBar1.Panels(1).Picture = Icons.ImageList1.ListImages.Item("Alert").Picture
        SearchSettings.StatusBar1.Panels(1).Text = "Please select at least one search criterion."
        Exit Sub
    End If


    'Search & filter
    With Sheets("Catalog")
    .Unprotect
    .AutoFilterMode = False
    
        If ctypeli >= 1 Then   '(Search for Cut Type)
            .Range("A6").AutoFilter Field:=3, Criteria1:=ctype, VisibleDropDown:=False
        End If


        If sizeli >= 1 Then  'Search for Units & Size
            .Range("A6").AutoFilter Field:=5, Criteria1:=Size, VisibleDropDown:=False
        End If
...

Note that the list index must be 1 or greater to apply the autofilter in my application because I use the word "Any" as the 1st list item (list index =0) in my comboboxes. In most cases, however, you would probably use > -1 (or >=0) to test whether a combobox is being used or not.

CJ

 

[JonnyAngelo]

Thank you for your time CJ,

I think that these two sections of the code may help me.
Only problem is, i'm not sure on where to implement it.
Would i have to also create a module for it or would it be okay
to use within the 'cmdFilter_Click' section and replace my old code?

 

[MrIfOnly]

If cmdFilter_Click is the only way that the search will be performed, it's perfectly okay to put the code in that event. If you are going to have other methods of calling the search then it's best to have it in a standard module and call it from the cmdFilter_Click event. I like to have my modules setup like you see in that file because it's easy for me to find things in my code at a later time. But it's just a personal preference really.