Solved: Auto filter for drop down list

[DarReNz]

Hi,

I have a column Stat that has a drop down list that contains values Pend, Wait etc in B25(Test Worksheet)

How do I search for those rows only and display in Out Worksheet when those values(eg. Pend) are chosen ?

If this is not clear, what I meant was instead of manually selecting the auto filter that I want on Out Worksheet, I wish to do it on another worksheet (Test) by using values in the drop down list that correspond to the auto filters.

I have thought of an idea but not sure if it can be done which is to link the auto filter values from Out Worksheet into a drop down list on Test Worksheet. Can you help ? Thanks

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$B$25" Then
With Target
Worksheets("Out").Columns("C:C").AutoFilter Field:=1, Criteria1:=.Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[DarReNz]

Thanks xld, may I know the code of selecting from a drop down list instead of typing into the cell ?

[DarReNz]

erm now i know what's wrong ... i created the drop down list using the forms toolbox ... how do i code for that ? Thanks

[Bob Phillips]

erm now i know what's wrong ... i created the drop down list using the forms toolbox ... how do i code for that ? Thanks

Change it to a Data Validation dropdown. Forms dropdown is overkill here.

[DarReNz]

ok thanks xld this problem is solved.

[Bob Phillips]

ok thanks xld this problem is solved.

That is the beauty of DV, there is no code. Unlike an object dropdown like a control toolbox or forms dropsown, the DV works on the cell, so when you select a value from that dropdown, the cell contains that value. That in trn triggers a worksheet change event (except in Excel 97).