Problem with Search macro + data connections

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:

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

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

smichael said:

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

Click to expand...

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