Microsoft Office Forums - View Single Post

s_samira_21 · 01-10-2015, 03:56 AM

Hi,

I have some data in sheet 1 and I want to filter them using an userform.
column A: Name
column B: Num1
column C: Num2
in my user form I have a combobox to select Name, 2 textboxes to enter min and max for Num1, and 2 other textboxes to enter min and max for Num2.

after I Enter the data the following code will be run by clicking on Filter Button

Code:

Private Sub CommandButton1_Click()
     Dim EOR
     If TextBox2.Text <> Empty And TextBox1.Text > TextBox2.Text Then               
          MsgBox "Wrong Numbers"              
          Exit Sub        
     End If        

     If TextBox4.Text <> Empty And TextBox3.Text > TextBox4.Text Then             
          MsgBox "Wrong Numbers"              
          Exit Sub       
     End If         

     Sheet1.AutoFilterMode = False        
     Sheet1.Range("A2:I1").AutoFilter            

     EOR = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row           
     Sheet1.AutoFilterMode = False      
     Sheet1.Range("A2:C" & EOR).AutoFilter         

     With Sheet1.Range("A3:C" & EOR)      
          If ComboBox1.Text <> Empty Then .AutoFilter Field:=1, Criteria1:="=" & ComboBox1.Text              
          If TextBox1.Text <> Empty Then .AutoFilter Field:=2, Criteria1:=">=" & TextBox1.Value               
          If TextBox2.Text <> Empty Then .AutoFilter Field:=2, Criteria1:="<=" & TextBox2.Value               
          If TextBox3.Text <> Empty Then .AutoFilter Field:=3, Criteria1:=">=" & TextBox3.Value               
          If TextBox4.Text <> Empty Then .AutoFilter Field:=3, Criteria1:="<=" & TextBox4.Value        
     End With   
End Sub

But it doesn't work correctly

please help me

the combobox and textboxes can be empty.

if it is better to use Advanced filter, Please help me with that.