Autofilter filter on combo change

[pedie]

I am trying to autofilter filter on combo change but it is giving runtime error 1004; autofilter if range class failed.
after that i want it to show in listbox1. which i think should be okay if this filtering works.

Thanks
Pedie

Private Sub ComboBox3_Change()
Sheet1.Select
Range("A1").Select
        Selection.AutoFilter
        Selection.AutoFilter Field:=1, Criteria1:=ComboBox3.Value
End Sub

 

[pedie]

sorry for sort of bumpng up but it still wont work and i am spending lots of time i guess...

Private Sub ComboBox3_Change()
Sheet1.Activate
Sheet1.Select
With ActiveSheet
            .AutoFilterMode = False
            .Range("A1:D1").AutoFilter
            .Range("A1:D1").AutoFilter Field:=1, Criteria1:=ComboBox3.Text
    End With
End Sub

 

[Tinbendr]

http://www.mrexcel.com/forum/showthread.php?t=54179
http://www.mrexcel.com/forum/showthread.php?t=342264
http://www.mrexcel.com/forum/showthread.php?t=490500&highlight=COMBOBOX+AUTOFILTER
http://www.mrexcel.com/forum/showthread.php?t=484833&highlight=COMBOBOX+AUTOFILTER

 

[pedie]

David, thanks for suggesting the links.
I went through all the link but wont let me come up with the working code

The code below first filters it and then give same error....
I am hoping someone would please write a running smooth code....
PS. I am running this code from userform1 combo change selection...
error:

runtime error 1004; unable to get the current region property...of class range

Private Sub ComboBox3_Change()
Dim x As String
x = ComboBox3.Value
Sheets("Sheet1").Activate
Rows(1).CurrentRegion.AutoFilter Field:=1, Criteria1:=x
End Sub

 

[meldoc]

Hi Pedie,

Not my area but try this.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ComboBox1_Change()<br>  <SPAN style="color:#00007F">With</SPAN> Sheet1.Range("A1")<br>    .AutoFilter<br>      MsgBox ComboBox1.Value <SPAN style="color:#007F00">'remove after testing!!!</SPAN><br>    .AutoFilter Field:=1, Criteria1:=ComboBox3.Value<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[pedie]

Maybe i didnt know how to put them together... it still wont work at first but when i added

on error resume next
on error goto 0

it is working....

Thanks for putting thoughts together.

Thanks Brian & everyone!

Pedie

 

[Tinbendr]

When you specify the Rowsource in a userform/combobox, you shouldn't use autofilter. During the change event, the combobox is trying to update the rowsource and is in conflict with the autofilter.

However, if you populate the combobox 'manually',

Private Sub UserForm_Initialize()

With Me.ComboBox1
For Each iRow In Sheet1.Range("A2:A7")
    .AddItem iRow
Next
End With

End Sub

Then

Rows(1).CurrentRegion.AutoFilter Field:=1, Criteria1:=ComboBox1.Value

works as expected.