Can This Macro Be Turned into Function

The macro below allows the filtering of a list based on the value in a cell (B2) on the queries page of the spreadsheet.

I am thinking it would look like DGET since it would be great if you give the name of the heading in the list to tell what field should be searched.

---------------------------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        ActiveSheet.ListObjects("List1").Range.AutoFilter Field:=2, Criteria1:= _
        "=" & Range("B2"), Operator:=xlAnd
    End If
    Application.EnableEvents = True
End Sub
List-of-Books.xls
LVL 1
Alex CampbellAsked:
Who is Participating?
 
nutschConnect With a Mentor Commented:
Check out the attached file on the queries tab and see if that works. Pick column in dropdown cell a1, enter the filter in cell b1.

Thomas
List-of-Books-3-.xls
0
 
FernandoFernandesCommented:
function ? you mean user defined function ? no, UDF's cannot do changes or execute methods of the range object...
0
 
nutschCommented:
Use this and you can update in A2 and B2

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Range("A2:B2")) Is Nothing Then
        If Len(Target) = 0 Then
            ActiveSheet.ListObjects("List1").Range.AutoFilter
        Else
            ActiveSheet.ListObjects("List1").Range.AutoFilter Field:=Target.Column, Criteria1:= _
                "=" & Target, Operator:=xlAnd
        End If
    End If
    Application.EnableEvents = True
End Sub

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Alex CampbellAuthor Commented:
I guess I am asking for it be interactive if UDFs don't work.
I don't want to have edit the macro if the cells involved are not the ones in the macro.
Can the macro ask for the location of the query field title and the query value?
0
 
Alex CampbellAuthor Commented:
While this macro does allow me to choose more than one column to query on, I was looking for a way to tell it a particular column by number of the column or the column name in the list to use for the query:

"I am thinking it would look like DGET since it would be great *if you give the name of the heading in the list to tell what field should be searched*. "

0
 
nutschCommented:
So if you had one field for column name and one field for filter value, that would work for you?

T
0
 
Alex CampbellAuthor Commented:
That's it exactly!
0
 
Alex CampbellAuthor Commented:
Very useful!!
Gives me the flexibility I was looking for.  I was interested in this because I get lists where the data I am searching for may be in wrong column. This is great!!
0
 
nutschCommented:
Glad we were able to figure out what you needed. Thanks for the grade.

Thomas
0
 
Alex CampbellAuthor Commented:
Same here. All the best.
Alex
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.