Microsoft Office Forums - View Single Post - [Solved] filter using a table list with VBA

trevorc · 11-07-2018, 12:52 PM

Hi All,
I am trying to improve on some code I have, currently the code below works fine, but if I need to add a condition to the list for the filter I also have to add it to this code. Is it possible to use a table list for the filter?
It also seems to have a problem with Capital letters - with/without requires 2 entries, can I also fix this other than with a list from a table?

Code:

Sub Filter_Closed()
t = ActiveSheet.Shapes("fltr").TextFrame.Characters.Text
     If ActiveSheet.Shapes("fltr").TextFrame.Characters.Text = "Filter Closed" Then
        ActiveSheet.ListObjects("Customers").Range.AutoFilter Field:=3, _
        Criteria1:=Array("Done, waiting for confirmation of payment", _
        "In Transit", "Received for Repair, Proceed with Repair", _
        "waiting delivery of parts", "Waiting for Email Address", "Waiting for reply from customer", "="), Operator _
        :=xlFilterValues
        ActiveSheet.Shapes("fltr").TextFrame.Characters.Text = "Show All"
      Exit Sub
      Else
        ActiveSheet.ListObjects("Customers").Range.AutoFilter Field:=3
        ActiveSheet.Shapes("fltr").TextFrame.Characters.Text = "Filter Closed"
      End If
End Sub