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