Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter with optional criteria
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter with optional criteria
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 --------------- Jacob Skaria "Basta1980" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter with optional criteria
Jacob,
Thanks, but will this setup make it possible to leave textbox 3 and 4 blank and still return data?! regards "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Basta1980" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter with optional criteria
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 --------------- Jacob Skaria "Basta1980" wrote: Jacob, Thanks, but will this setup make it possible to leave textbox 3 and 4 blank and still return data?! regards "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "Basta1980" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilter Criteria | Excel Programming | |||
AutoFilter Criteria in VBA | Excel Programming | |||
Selection.AutoFilter Field / Criteria = criteria sometimes non-existing on worksheet | Excel Programming | |||
AutoFilter criteria | Excel Programming | |||
VBA Autofilter Criteria | Excel Programming |