CODEWITHSUNDEEP
excelvbaexcel-2007
Ghokun
Ghokun

Reputation: 3465

Macro button to change filter

I want to have buttons to change my excel filter.

I filter my fields with contains=something so each button should change that something text.

Button1: change filter to contains=button1

Button2: change filter to contains=button2

and so on..

Upvotes: 2

Views: 21443

Answers (3)

Pawel
Pawel

Reputation: 1

I would add a HLOOKUP in e.g. cell h2 that would be controlled by the spin button changing field F1. in this case we have a full solution 

 =HLOOKUP(h2;h2:h100;F1;0)

This is combined with this macro provided above:

Sub Filter()
     Dim searchField As String

     searchField = "=*" & Range("H2") & "*"

    ActiveSheet.Range("$A$3:$H$18401").AutoFilter Field:=8, Criteria1:= _
        searchField, Operator:=xlAnd

 End Sub

Upvotes: 0

Aamir
Aamir

Reputation: 11

You could make it easier by referencing the the filter search string from a cell as follows.

Sub Filter()
    Dim searchField As String

    searchField = "=*" & Range("H2") & "*"

    ActiveSheet.Range("$A$3:$H$18401").AutoFilter Field:=8, Criteria1:= _
        searchField, Operator:=xlAnd

End Sub

Upvotes: 1

Ghokun
Ghokun

Reputation: 3465

Since I found answer myself, I put it here for future help.

    Sub AI()

    '            sheet range and which field you want to filter and criteria to search
        ActiveSheet.Range("$A$2:$Z$203").AutoFilter Field:=14, Criteria1:="stringtomatch"
    End Sub

Upvotes: 3

Related Questions