Excel Table filtering using a drop down list-CodePudding

I have a spreadsheet with a drop down list in one of the cells, I want to use values from the list to apply auto filter to the table.

I have tried a bunch of VBA subs but nothing seems to be doing the trick for me. I would be grateful if someone explained in foolproof terms how to achieve this.

 Range("Orders[[#Headers],[Order Status]]").Select
ActiveSheet.ListObjects("Orders").Range.AutoFilter Field:=5, _
    Criteria1:=Range("B4").Value

Above the last sub I have attempted to use to achieve this. I applied a named range Orders to the table.

CodePudding user response：

You have to add code for the worksheet_change event which gets triggered whenever you change the value of B4.

I gave B4 a name to be more readable: Filtervalue Furthermore it's not necessary to select the table before filtering.

'This goes into the worksheets module 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Name Is Nothing Then
    If Target.Name.Name = "Filtervalue" Then
        'if Filtervalue has changed call filter routine  
        filterTable
    End If
End If
End Sub


Private Sub filterTable()

Dim FilterValue As String
FilterValue = ThisWorkbook.Names("Filtervalue").RefersToRange

Dim lo As ListObject
Set lo = Me.ListObjects("tblKommentare")

lo.Range.AutoFilter Field:=5, Criteria1:=FilterValue

End Sub