Dynamic search and filtering in Excel

Currently I am experimenting with dynamic search bars and filtering in Excel. I have found a nice tutorial that uses a table and Active-X text box. I have recreated it and surely it works. My problem however is that it only takes into account one column. Now, I have created another table and another search bar, thinking that it would work just as fine, however, for some reason it does not. Here is the problem: The data set looks like this; enter image description here Searching for cells containing "1" in "addr1"(works normal): enter image description here Searching for cells containing "1" in "addr2"(does not work): enter image description here

I have tried setting the format to Text, general, number etc. but nothing seems to have any effect. The second search should list the cells containing "123" as well but it doesn't.

VBA Code in the Sheet:

Private Sub TextBox1_Change()
ActiveSheet.ListObjects("address1").Range.AutoFilter Field:=1, Criteria1:="*"  & [A1] & "*", Operator:=xlFilterValues

End Sub

Private Sub TextBox2_Change()
ActiveSheet.ListObjects("address2").Range.AutoFilter Field:=1, Criteria1:="*" & [B1] & "*", Operator:=xlFilterValues
End Sub

Anybody has any solution?

My sample file can be found here:

Thank you for any feedback!


ANSWERS:


In the second code:

Private Sub TextBox2_Change()
ActiveSheet.ListObjects("address2").Range.AutoFilter Field:=1, Criteria1:="*" & [B1] & "*", Operator:=xlFilterValues
End Sub

You should change Field:=1 to Field:=2, otherwise you'll be searching the content at TextBox2_Changeat addr1 column.


The problem you are facing for now is that the filter "*1*" will only match strings, not numbers. Even if you format your cells as Text, the already entered numbers will be still parsed as numbers; they wont be converted to strings. You have two ways to enter a number as string:

  • enter it in to the cell after you had formatted it as Text

  • enter it preceded with some ' (single quote). i.e. '123. This would force Excel to take it as a string, even if the format of the cell is General.

For the time being you may write a simple macro to convert all you table's data into strings and proceed from there.

Finally I don't understand why you define a different table for each of the three adjacent columns. You should make only one table with three columns.


Try these two samples. See the images below to get a sense of how these work.

enter image description here

enter image description here



 MORE:


 ? Dynamic TableView Swift 3 With Beginning Graph
 ? Switch case is not working in Reactjs
 ? Dynamic Polymorphism's weird output
 ? which technologies to use for forms created dynamically?
 ? Winforms - Dynamic Load / Save Settings
 ? Winforms - Dynamic Load / Save Settings
 ? Winforms - Dynamic Load / Save Settings
 ? Save Settings in a .NET Winforms Application
 ? C# Setting load and save
 ? Winforms preferences values