Excel VBA to filter a table based on multiple search criteria entry in ActiveX Control Textbox

[alex_n]

Hello All,

(This is a development of the thread Excel 365 ActiveX Textbox for "Search as you type" not working on second monitor - Microsoft Communi...

I am seeking a solution to the below problem:

I have an excel file with materials pricing table - this is a snapshot of the main one, the main one consists of over 100K line items. 

I want to be able to search an item with a search-as-you-type style and i want to be able to type at least three criteria delimited with a comma. 

Currently, it works only with two criteria. Once I put the second comma to enter the third criteria, it resets the search starts the search anew. Please assist me in finding the solution.

Here is the link to the file: Sample - Instant Search Filter v02.xlsm

Thanks in advance.

Al.

[#2]

@alex_n 

AutoFilter cannot handle more than 2 criteria with wildcards.

Advanced Filter would be an alternative - you can assemble the criteria range in VBA.

Two other options are described in AutoFilter based on an array, more than 3 elements 

[#3]

Hans, thanks for the response. I think the version 2 solution posted on stackoverflow will work, but how do I link it to work with my file?

[#4]

@alex_n 

Do you want to show a row if

(1) the column contains ALL of the keywords, or

(2) the column contains at least ONE of the keywords?

[#5]

Hans, I want to show all rows that contain ALL keywords on AND basis.

Thanks,
Al

[#6]

@alex_n 

See if the attached version does what you want.

[#7]

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.

[#8 · Accepted solution]

@alex_n 

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.

[#9]

Hans, 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.

[#10]

Hans, 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.

[#11]

@alex_n 

The attached version uses a helper column named Aux to filter on.

You can hide this column if you wish.

[#12]

Is it possible to achieve that without adding a new column as I am using a power query output table?

[#13]

@alex_n 

I don't know. Maybe someone else can help.

[#14]

@alex_n 

https://b23.tv/2OzKnr7

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);
}
;

[#15]

@alex_n 

Try this:
It's similar to what @Hans Vogelaar Hans proposed. It uses a temporary helper column, but the column is not part of the table. I put it 2 column next to the table.

How it works:
It uses a temporary helper column, but the column is not part of the table. I put it 2 column next to the table.
It's a live search i.e. the list will be filtered as you type
Type some keywords (it can be more than 2) in the textbox, separated by a space, e.g "ma la", the search ignores the keywords order, so keywords "ma la" will match "Maryland" and "Alabama"
The result will be displayed after you type the second character. The reason behind this is to speed up the search. Searching with 1 character will return the most results, and on large data sets it can give you a noticeable lag. Searching by 2 characters or more will be faster.
You can set this behavior in this part of the code:
Private Const CN As Long = 2 'start searching after typing certain number of character, change to suit

Speed test:
I tested it on 148K rows of data, and it gave me the result almost instantly.

The code:

'https://techcommunity.microsoft.com/t5/excel/excel-vba-to-filter-a-table-based-on-multiple-search-criteria/m-p/3812945
Option Explicit

Private va, vb
Private cell_Start As Range
Private oldVal As String

Private Const CN As Long = 2  'start searching after typing certain number of character

Private Sub CommandButton1_Click()
If Not cell_Start Is Nothing Then
    cell_Start.EntireColumn.Clear
End If
TextBox1.Text = ""
End Sub

Private Sub TextBox1_GotFocus()
    
    'load column 2 data to va
    With Sheets("Sheet1").ListObjects("Materials")
        va = .DataBodyRange.Columns(2).Value
        
        Set cell_Start = .Range.Cells(1).Offset(, .Range.Columns.Count + 1)
        cell_Start.Value = "Tag"
    End With
    
    Call add_1
         
End Sub

Private Sub add_1()

Dim i As Long
Dim t
ReDim vb(1 To UBound(va, 1), 1 To 1)
For i = 1 To UBound(vb, 1)
    vb(i, 1) = 1
Next
End Sub

Private Sub TextBox1_Change()
Dim tx1 As String

On Error GoTo skip:

        
    Application.EnableEvents = False
    Application.ScreenUpdating = False
        
    With TextBox1
 
            tx1 = UCase(Trim(.Value))
'
            If Len(tx1) < CN Then
                oldVal = tx1
                Call add_1
                cell_Start.Resize(UBound(vb, 1) + 1, 1).AutoFilter
                cell_Start.Offset(1).Resize(UBound(vb, 1), 1) = ""
            
            Else
            
                   If tx1 <> oldVal Then
    
                       If InStr(1, tx1, oldVal, vbBinaryCompare) = 0 Then Call add_1
                       
                       Call get_filterX
                        
                       cell_Start.Resize(UBound(vb, 1) + 1, 1).AutoFilter
                       cell_Start.Offset(1).Resize(UBound(vb, 1), 1) = vb
                       cell_Start.Resize(UBound(vb, 1) + 1, 1).AutoFilter Field:=1, Criteria1:="1"
                    
                   End If

            End If
        
        oldVal = tx1
    End With

        Application.EnableEvents = True
        Application.ScreenUpdating = True
        
Exit Sub
skip:
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "Error number " & Err.Number & " : " & Err.Description

End Sub


Sub get_filterX()
'search without keyword order, case insensitive
Dim i As Long, x, z, q
Dim v As String
Dim flag As Boolean
    
    z = Split(Trim(UCase(TextBox1.Value)), " ")

    For i = 1 To UBound(va, 1)
            If vb(i, 1) = 1 Then
                v = UCase(va(i, 1))
                For Each q In z
                    If InStr(1, v, q, vbBinaryCompare) = 0 Then vb(i, 1) = 0: Exit For
                Next
            End If
    Next

End Sub

[#16]

@Cangkir 

When I copied the code to the actual file and right-sized it by changing the column number to 6 as the search items sit in column 6 it says there's a bug:

[#17]

Also, after applying the multi search, i could not apply filter to the table: it does not return the expected result

[#18]

@alex_n 

When I copied the code to the actual file and right-sized it by changing the column number to 6 as the search items sit in column 6 it says there's a bug:

To change the search column to column 6, change this part (in Private Sub TextBox1_GotFocus()):

va = .DataBodyRange.Columns(2).Value

to

va = .DataBodyRange.Columns(6).Value

Also, after applying the multi search, i could not apply filter to the table:
The filter actually isn't on the table but in the helper column. 

Why do you need to apply filter to the table after applying the multi search?
it does not return the expected result
Is it on my sample workbook or on your actual workbook?
Can you give an example?