Filtering data in listbox using two combo boxes

matix003

New Member
Joined
Mar 18, 2022
Messages
21
Office Version
  1. 365
Platform
  1. Windows
I have a userform listbox where I want to filter data using two parameters - ALL, Parameter1, Parameter2. With the code that I have written I am only able to filter data using Parameter1 not Parameter2 or All. By default, 'All' is selected which should show all records in the listbox. There are 3 combo boxes. One is to select the parameter by which the data should get filtered, second one is for selecting Parameter1 values and third one is for Parameter2 values. No data is displayed for 'All'. When I select 'Parameter1' and then select any value the data gets filtered and displayed correctly. When I select 'Parameter2' and then I get an error message saying - "Run-time error '1004': Unable to get the match property of the WorkSheetFunction class". How do I resolve this error? How do I get the data filtered correctly for Parameter2 and 'All'? Here is my code -

VBA Code:
Sub Manage_Emp_Display()
    
    Dim dsh As Worksheet
    Set dsh = ThisWorkbook.Sheets("EMPMasterDisplay") '''sheet to paste filtered data
    dsh.Cells.Clear

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("EMPMaster") '''has the source data
    sh.AutoFilterMode = False
    
    If Me.cmb_filter.Value = "Parameter1" Then
        temp1 = Me.cmb_BA_Parameter1.Value
        sh.UsedRange.AutoFilter Application.WorksheetFunction.Match(Me.cmb_filter.Value, sh.Range("1:1"), 0), temp1
        Me.cmb_BA_Parameter1.Value = ""
        
    End If
    If Me.cmb_filter.Value = "Parameter2" Then
        temp2 = Me.cmb_BA_Parameter2.Value
        sh.UsedRange.AutoFilter Application.WorksheetFunction.Match(Me.cmb_filter.Value, sh.Range("1:1"), 0), temp2
        Me.cmb_BA_Parameter2.Value = ""
    End If
    
    sh.UsedRange.Copy
    dsh.Range("A1").PasteSpecial xlPasteValues
    dsh.Range("A1").PasteSpecial xlPasteFormats
        
    sh.AutoFilterMode = False
    
    Dim last_row As Long
    last_row = Application.WorksheetFunction.CountA(dsh.Range("A:A"))
    If last_row = 1 Then last_row = 2
    
    With Me.ListBox4
        .ColumnHeads = True
        .ColumnCount = 8
        .ColumnWidths = "0,150,60,140,70,60,50,80" 
        .RowSource = dsh.Name & "!A2:H" & last_row
    End With
End Sub

VBA Code:
Private Sub Image9_Click() ''These images are search icons next to the combo box 

Call Manage_Emp_Display

End Sub

VBA Code:
Private Sub Image10_Click()

Call Manage_Emp_Display

End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
My bad!? The code works for Parameter2. I had made a spelling mistake. How do I get all the data displayed for 'All'?
 
Upvote 0

Forum statistics

Threads
1,214,951
Messages
6,122,442
Members
449,083
Latest member
Ava19

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
Back
Top