Autofilter with optional criteria

[Basta1980]

Hi there,

I'm using an userform which gives an user the oppurtunity to fill in data
which is used to run the autofilter. There are 4 boxes the user can fill in.
One box (combobox1) is not optional, the rest (all the textboxes) is optional
i.e. the user gets to choose if they fill in the designated boxes or not. The
code below works when all boxes are filled in, but how do I get them to be
optional

Private Sub CommandButton1_Click()

TextBox1.SetFocus
Worksheets(ComboBox1.Value).Range("A1").AutoFilter _
field:=1, _
Criteria1:=TextBox1.Value
Worksheets(ComboBox1.Value).Range("A1").AutoFilter _
field:=5, _
Criteria1:=TextBox2.Value
Worksheets(ComboBox1.Value).Range("A1").AutoFilter _
field:=7, _
Criteria1:=TextBox3.Value
Worksheets(ComboBox1.Value).Activate

End Sub

Thnx in advance,

Basta1980

 

[Jacob Skaria]

One way is to use IF statement..

If Trim(TextBox2.Value) <> "" Then
Worksheets(ComboBox1.Value).Range("A1").AutoFilter _
field:=5, Criteria1:=TextBox2.Value
End If

If this post helps click Yes

 

[Basta1980]

Jacob,

Thanks, but will this setup make it possible to leave textbox 3 and 4 blank
and still return data?!

regards

 

[Jacob Skaria]

Do you mean..the below

Dim ws As Worksheet
Dim rngTemp As Range
Set ws = Worksheets(ComboBox1.Value)
Set rngTemp = ws.Range("A1")
rngTemp.AutoFilter Field:=1, Criteria1:=TextBox1.Value
If Trim(textbox2.Value) <> "" Then _
rngTemp.AutoFilter Field:=5, Criteria1:=textbox2.Value
If Trim(TextBox3.Value) <> "" Then _
rngTemp.AutoFilter Field:=7, Criteria1:=TextBox3.Value
If Trim(TextBox4.Value) <> "" Then _
rngTemp.AutoFilter Field:=9, Criteria1:=TextBox4.Value
ws.Activate

If this post helps click Yes