Filtering with VBA out of user forms.

[cardinalphin]

Good morning,

I'm creating a spreadsheet with a userform to filter data. The afflicted portion of the userform is basically just a bunch of comboboxes stacked on top of each other. The combo boxes are populated from several different tables. After the first combobox is selected, I'm hoping to make a few things happen:

1) Populate the combobox below it with a child list; a named table of the same name as the previous selection (from a different worksheet). This aspect, thanks to Norie, works flawlessly.
2) Filter a column in another worksheet using the name that was just selected.

Step 2 is where I'm running into an issue. Instead of filtering based on the value of the combobox the user selects, my code is instead filtering the bottom value held within the table of the child list. For instance:

From the top level combbox, a user selects CTM_Leaflets. My intent is to then filter column A to only CTM_Leaflets. Instead of that though, the script is actually filtering with CTM_Leaflets_Other, which is the bottom value of the CTM_Leaflet named table (and what populates the list at the second level combobox).

I'm using the following code to perform this operation:

Private Sub compListLevel1_Change()

    'Select the data tab, enable filters on row 1, and filter based on the value of the list selection
    Worksheets("Data").Activate
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$GR$5").AutoFilter Field:=1, Criteria1:=Range(compListLevel1).Value
    
    
    'Use the parent selection to set value of the parent list
    compListLevel2.List = Range(compListLevel1.Value).Value
End Sub

Again, I'm not having any issues with the bottom portion of the code, only the top part where the filtering is happening.

Thank you so much for taking the time to look at this and I greatly appreciate any help that you're able to offer. Have a great day!

 

[cardinalphin]

Just in case someone comes across this later, I wanted to update the post to show what was wrong with the code before as I've got everything working right now...

Original Code:

    ActiveSheet.Range("$A$1:$GR$5").AutoFilter Field:=1, Criteria1:=Range(compListLevel1).Value

Fixed Code:

    ActiveSheet.Range("$A$1:$GR$5").AutoFilter Field:=1, Criteria1:=compListLevel1.Value

Hope that helps in case anyone takes a look at this later!