Forum Discussion
alex_n
May 04, 2023Brass Contributor
Excel VBA to filter a table based on multiple search criteria entry in ActiveX Control Textbox
Hello All, (This is a development of the thread Excel 365 ActiveX Textbox for "Search as you type" not working on second monitor - Microsoft Community Hub) I am seeking a solution to the belo...
- May 08, 2023
That is unavoidable; the code has to do a lot of work. In that case, I'd use a command button to filter, instead of reacting to every keystroke.
alex_n
May 08, 2023Brass Contributor
Hans, thanks for the response. It works on the smaller data sample, but the only issue I have is when I tried it on a large data set: it freezes after each key stroke in the text box.
HansVogelaar
May 08, 2023MVP
That is unavoidable; the code has to do a lot of work. In that case, I'd use a command button to filter, instead of reacting to every keystroke.
- peiyezhuJun 01, 2023Bronze Contributor
How about play with database?
//select * from multiple_criterias_filter; cli_add_html~ <form method="post" action="?p=Tools/DisTbl&g=sql" autocomplete="on" autofocus="autofocus" > <input type="search" name="Items"> <input type="submit" id="submitForm" value="Do Filter" style="height:2em"> </form>; cli_add_php~ //$_POST["Items"]="Fix,lig"; if(isset($_POST["Items"])){ $arrItems=explode(",",$_POST["Items"]); $s=array_reduce($arrItems,function($carry,$v){ $out=$carry." and "."Items like '%$v%'"; return $out; }); $key=str_replace(',','|',$_POST["Items"]); $sql="select PO,udf_highlightbg('$key',Items) Items,UOM,Qty,Unit_Price,Total_Price from multiple_criterias_filter where 1=1 $s"; echo $_POST["Items"]; \multiquery\display_table($sql); } ;
- HansVogelaarMay 31, 2023MVP
I don't know. Maybe someone else can help.
- alex_nMay 31, 2023Brass ContributorIs it possible to achieve that without adding a new column as I am using a power query output table?
- HansVogelaarMay 31, 2023MVP
The attached version uses a helper column named Aux to filter on.
You can hide this column if you wish.
- alex_nMay 31, 2023Brass ContributorHans, I am applying filter to the result and the table does not retain the search result. What would be a possible solution for this? Thanks in advance.
- alex_nMay 22, 2023Brass ContributorHans, thanks for the solution. I am using this in combination with the two-entry search field, this will give the user an option to use two-search field and/or multiple-criteria search field.
Regards,
Al.