ThermalStress
New Member
- Joined
- Jan 1, 2018
- Messages
- 3
Searching for the meaning of acronyms at my company was tricky. Everyone likes Excel, so I pooled the data into a table, with the header range: "Header" consisting of cell entries "Acronym", "Description", "Notes". Above it, I have ActiveX controls "SearchBox", "SearchDesc", "SearchButton", "ClearFilter", "AddFormButton" respectively (as per the image).
Currently, entering either an acronym in the SearchBox, a string in SearchDesc, or both, will clear the previous filter, then use a new AutoFilter to return the results of the search, and message the user if they were successful in finding something and how many results they have. If there's no results, it can bring up a UserForm to add a new entry. It actually works very well.
However, someone beta'd it for me and instantly tried to search multiple keywords in the description textbox. This caused an error because AutoFilter only allows 1 or 2 criteria. I tried creating an array of keywords as 1 criteria but failed.
So now I want the ActiveX textbox "SearchDesc" to allow not just 1 or 2, but any amount of search terms, including wildcards, so that the user can search however they like.
This forum has pointed me towards Advanced Filter (which allows more than 2 criteria) however, all the examples I've found use criteria written in worksheet cells within a 2 row table (essentially a copy of the header, with the criteria below). I want a seamless search within VBA, not relying on using new sheets or hidden tables or writing to arbitrary cells then deleting. I've been on this for days, scouring the websites and forums trying to find a solution.
How do you use VBA to filter a table, using multiple keywords entered in any order into a textbox?
Currently, entering either an acronym in the SearchBox, a string in SearchDesc, or both, will clear the previous filter, then use a new AutoFilter to return the results of the search, and message the user if they were successful in finding something and how many results they have. If there's no results, it can bring up a UserForm to add a new entry. It actually works very well.
However, someone beta'd it for me and instantly tried to search multiple keywords in the description textbox. This caused an error because AutoFilter only allows 1 or 2 criteria. I tried creating an array of keywords as 1 criteria but failed.
So now I want the ActiveX textbox "SearchDesc" to allow not just 1 or 2, but any amount of search terms, including wildcards, so that the user can search however they like.
This forum has pointed me towards Advanced Filter (which allows more than 2 criteria) however, all the examples I've found use criteria written in worksheet cells within a 2 row table (essentially a copy of the header, with the criteria below). I want a seamless search within VBA, not relying on using new sheets or hidden tables or writing to arbitrary cells then deleting. I've been on this for days, scouring the websites and forums trying to find a solution.
How do you use VBA to filter a table, using multiple keywords entered in any order into a textbox?
