microwaveref
06-03-2010, 05:43 PM
Hi, I'm trying to modify a code that I got here in your forum to fit my file, but I kept on getting an error and can't figure out what to do
I wanted to use my combobox(active x control) as my criteria in my filter
My ComboBox1 is located in J7, and is also linked in cell J7.
The criteria to filter is in C9
The range to filter is from C9 to J9
And what I want is when "ALL" is selected in my ComboBox, the filter will be refreshed.
Thanks! I hope you can help me resolve my issue.
Sub Filter()
Dim fld
Application.ScreenUpdating = False
If UCase(Range("J7").Value) <> "ALL" Then
Range("C9:J9").AutoFilter Field:=2, Criteria1:=Sheets("SEARCH").ComboBox1.Value, visibledropdown:=False
With ActiveSheet.AutoFilter
For fld = 3 To 9
If Not .Filters(fld).On Then
Range("C9:J9").AutoFilter Field:=fld, visibledropdown:=False
End If
Next fld
End With
Else
IF
Sheets("SEARCH").ComboBox1.Value = "ALL"
Range("C9:J9").AutoFilter Field:=fld, visibledropdown:=False
Range("A1").Select
End if
End If
End Sub
I wanted to use my combobox(active x control) as my criteria in my filter
My ComboBox1 is located in J7, and is also linked in cell J7.
The criteria to filter is in C9
The range to filter is from C9 to J9
And what I want is when "ALL" is selected in my ComboBox, the filter will be refreshed.
Thanks! I hope you can help me resolve my issue.
Sub Filter()
Dim fld
Application.ScreenUpdating = False
If UCase(Range("J7").Value) <> "ALL" Then
Range("C9:J9").AutoFilter Field:=2, Criteria1:=Sheets("SEARCH").ComboBox1.Value, visibledropdown:=False
With ActiveSheet.AutoFilter
For fld = 3 To 9
If Not .Filters(fld).On Then
Range("C9:J9").AutoFilter Field:=fld, visibledropdown:=False
End If
Next fld
End With
Else
IF
Sheets("SEARCH").ComboBox1.Value = "ALL"
Range("C9:J9").AutoFilter Field:=fld, visibledropdown:=False
Range("A1").Select
End if
End If
End Sub