EXCEL: Autofilter method via Combo box gives me error 1004 ! 1

[franksirvent]

Hi there
I have spent hours on this and can't understand what's going on...
i have a combobox in EXCEL with 3 options, after any of the options is selected it runs the following code.
It runs ok HOWEVER the first time you run it, the user gets an error:
Error :1004, Failure on Method Autofilter of Range Class ?
Whatever that means?
If you ignore the message, nothing happens, and the next time you run the code, it correctly filters the rows....

Can anyone help? I am desperate?

thanks

Private Sub ComboBox1_Change()
On Error GoTo combobox1_error
Dim The_date As Long
The_date = Date
If ComboBox1 = "IN TRANSIT" Then
Selection.AutoFilter Field:=14
Selection.AutoFilter Field:=14,_ Criteria1:=">=" & The_date, Operator:=xlOr, Criteria2:=""

ElseIf ComboBox1 = "DELIVERED" Then

Selection.AutoFilter Field:=14
Selection.AutoFilter Field:=14, _Criteria1:="<=" & The_date, Operator:=xlAnd

ElseIf ComboBox1 = "ALL" Then
Selection.AutoFilter Field:=14
Selection.AutoFilter Field:=14,_ Criteria1:="<=" & The_date, Operator:=xlOr, Criteria2:=""
'.AutoFilter Field:=3, Criteria1:=">=" & The_date, Operator:=xlAnd

End If

Exit Sub

combobox1_error:
If Err.Number = 1004 Then
Else

End If
End Sub

 

[SkipVought]

Hi,

I would NOT use the Selection object. Rather explicitly reference a range within the table, preferably the TopLeft cell. You were also missing some punctiuation in the correct syntax. Also make sure the autofilter is on.

Private Sub ComboBox1_Change()
    On Error GoTo combobox1_error
    Dim The_date As Long
    The_date = Date

    If Not oSomeSheet.AutoFilterMode Then oSomeSheet.[a1].AutoFilter


    If ComboBox1 = "IN TRANSIT" Then
                    oSomeSheet.[A1].AutoFilter Field:=14
                    oSomeSheet.[A1].AutoFilter Field:=14, _
                    Criteria1:=">=" & The_date, Operator:=xlOr, Criteria2:=""
    
    ElseIf ComboBox1 = "DELIVERED" Then
    
                    oSomeSheet.[A1].AutoFilter Field:=14
                    oSomeSheet.[A1].AutoFilter Field:=14, _
                    Criteria1:="<=" & The_date, Operator:=xlAnd
                
    ElseIf ComboBox1 = "ALL" Then
                    oSomeSheet.[A1].AutoFilter Field:=14
                    oSomeSheet.[A1].AutoFilter Field:=14, _
                    Criteria1:="<=" & The_date, Operator:=xlOr, Criteria2:=""
                    '.AutoFilter Field:=3, Criteria1:=">=" & The_date, Operator:=xlAnd
    
    End If
    
    Exit Sub
    
combobox1_error:
    If Err.Number = 1004 Then
    Else
            
    End If
End Sub

Skip,

_[red][/red]

 

[franksirvent]

hi there and thanks for taking the time

I get an error on the following line

If Not oSomeSheet.AutoFilterMode Then oSomeSheet.[a1].AutoFilter

ERROR: 424 An object is needed

it doesn't do any further process (exists sub)

Filter is on

Does oSomeSheet needs to be changed for something else ?
Sorry my Excel VB knowledge is rather basic...

thanks

 

[SkipVought]

YOUR sheet object, whatever it is.

Skip,

_[red][/red]

 

[franksirvent]

sorry, I looked in the forum before asking again as I am not sure what you mean and didn't want to bug you again...

My filters are on row 5....
the worksheet is called "REPORT"
How do I do I refer to the oSomeSheet...

Sorry to be a pain

 

[SkipVought]

If your sheet name is REPORT, then the sheet object is...

Worksheets("REPORT")

Skip,

_[red][/red]

 

[franksirvent]

Yes !!!!

it works perfectly !!!

thank you so much for taking your time in helping me out with this.