Problem with Search macro + data connections

[smichael]

Well, I have a search macro that has been working great. There is a formula in the last column that is either "TRUE" or "FALSE" depending on if the row has a word matching the search. This is filtered through the macro and allows for only the "TRUE" rows to show up.

The problem I am having is that I would like to update this data through a connection I've set up, but it seems that this can only be executed by means of a table. With that, I was wondering if this code can be altered to allow it to search through a table without converting the data into a range.

The code is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
    If Range("A3") = "" Then
        On Error Resume Next ' in case autofilter not applied
        ActiveSheet.ShowAllData
    Else
        Range("A7:E1764").AutoFilter Field:=6, Criteria1:="TRUE"
    End If
End If
End Sub

Thanks for the help!

 

[baitmaster]

this issue is linked to previous thread http://www.mrexcel.com/forum/showthread.php?t=565010

I understand the issue to be that you want the search filter tool to work on an XL07 table instead of a defined range that has autofilter applied to it. I don't use data connections myself so I don't know any other implications caused by them, and can't help with that side of things

I wiped out the autofilter section, inserted a 6-column XL07 table (which by default is named "table1" by XL) and used this. I had to change how it was referred to in the VBA code, and also how the autofilter was removed from it, because showalldata doesn't work. I used the same T/F formula approach in column 6

My amended code is now:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
    If Range("A3") = "" Then
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=6
    Else
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=6, Criteria1:="TRUE"
    End If
End If
End Sub

To work out how XL was naming and using my table within the VBA, I simply recorded a macro, where I filtered the table once or twice and reset it, then used the appropriate piece of code. You can also use the names box on the top left of the screen, to identify table names

 

[smichael]

this works perfectly! thanks for all you're help baitmaster, you truly are a genuise in disguise

 

[baitmaster]

is it possible to run this macro through a button? i can't seem to get it to work

Please can you post further questions to the original thread, so other users get the benefit of any responses, Thanks

This is easy enough to do, but you need to change several things.

The original code is an event that is trigggered to run when the worksheet is changed, you need to change that. The first thing it does is to see if it should be run, based on what is being changed - that becomes unnecessary because you are running it manually. The auto event uses an object called "Target" as the range, currently we are using "A3", to see if this overlaps (intersects) with target - we don't need that test, we just use the range itself

We then configure this code to be run from a button, the method varies depending what type of button you use

Other improvements: I would use a named range instead of "A3", and I would move my formula to column 1 instead of column 6, as this enables easier update to more columns of data (col 6 might move, col 1 probably wont)

code becomes

Sub searchFilter()
    If Range("searchBox") = "" Then
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1
    Else
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="TRUE"
    End If
End Sub