Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

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
0
Alex Campbell
Asked:
Alex Campbell
  • 5
  • 4
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
nutschCommented:
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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now