wl_tinus
05-30-2006, 06:34 AM
For the persons with no or almost no excel knowledge in my office I want to make it easier to use autofilter.
To do this I'm trying to write a VBA code that takes the input from several fields and uses those variables in the autofilter fields. That part was easy to figure out.
It becomes difficult when you leave a field empty. Autfilter will give no results in stead of all results.
The database consists of 17 columns. I the 'menu' I'm building I want to give the user the option to filter on about five of those 17 columns (Example: Type, Size1, Size2, Age, Mileage). If one of those fields is left empty I want the autofilter to show alle data. (I'm won't be able to post the database, as I would most likely lose my job due to privacy issues)
I have this working code for two fields, but I was wondering if there is any smarter way to do this. The code becomes quite large and difficult for 5 or more criteria.
Private Sub CommandButton1_Click()
Dim CritDis As String
Dim CritGew As String
If Cells(3, 2) <> "" And Cells(2, 2) <> "" Then
CritDis = Cells(3, 2)
CritGew = Cells(2, 2)
Sheets("DATA").Select
Selection.AutoFilter Field:=16, Criteria1:=CritDis
Selection.AutoFilter Field:=10, Criteria1:=CritGew
ElseIf Cells(2, 2) <> "" And Cells(3, 2) = Empty Then
CritGew = Cells(2, 2)
Sheets("DATA").Select
Selection.AutoFilter Field:=10, Criteria1:=CritGew
ElseIf Cells(3, 2) <> "" And Cells(2, 2) = Empty Then
CritDis = Cells(3, 2)
Sheets("DATA").Select
Selection.AutoFilter Field:=16, Criteria1:=CritDis
Else
Sheets("DATA").Select
ActiveSheet.ShowAllData
End If
End Sub
If anyone could help me, I would really appreciate it.
Greetz,
Martijn
To do this I'm trying to write a VBA code that takes the input from several fields and uses those variables in the autofilter fields. That part was easy to figure out.
It becomes difficult when you leave a field empty. Autfilter will give no results in stead of all results.
The database consists of 17 columns. I the 'menu' I'm building I want to give the user the option to filter on about five of those 17 columns (Example: Type, Size1, Size2, Age, Mileage). If one of those fields is left empty I want the autofilter to show alle data. (I'm won't be able to post the database, as I would most likely lose my job due to privacy issues)
I have this working code for two fields, but I was wondering if there is any smarter way to do this. The code becomes quite large and difficult for 5 or more criteria.
Private Sub CommandButton1_Click()
Dim CritDis As String
Dim CritGew As String
If Cells(3, 2) <> "" And Cells(2, 2) <> "" Then
CritDis = Cells(3, 2)
CritGew = Cells(2, 2)
Sheets("DATA").Select
Selection.AutoFilter Field:=16, Criteria1:=CritDis
Selection.AutoFilter Field:=10, Criteria1:=CritGew
ElseIf Cells(2, 2) <> "" And Cells(3, 2) = Empty Then
CritGew = Cells(2, 2)
Sheets("DATA").Select
Selection.AutoFilter Field:=10, Criteria1:=CritGew
ElseIf Cells(3, 2) <> "" And Cells(2, 2) = Empty Then
CritDis = Cells(3, 2)
Sheets("DATA").Select
Selection.AutoFilter Field:=16, Criteria1:=CritDis
Else
Sheets("DATA").Select
ActiveSheet.ShowAllData
End If
End Sub
If anyone could help me, I would really appreciate it.
Greetz,
Martijn