VBA filter search box and clear button for numbers

[dancer5]

Hello,
I've been scouring the internet on how to create a search textbox with a clear button that will search one column in my table that has numerical data and filter the table for that. I keep running into issues with it not working for numbers or not being able to clear the search box or not working at all. As you can see, I did try to use a helper column to store the number as text but it wouldn't run.

The last iteration that did work somewhat (only worked for letters, but need it to work for numbers as data will be numbers:

Private Sub TextBox1_Change()
    Application.ScreenUpdating = False
   
    ActiveSheet.ListObjects("ScreenLog").Range.AutoFilter Field:=2, Criteria1:=[C2] & "*", Operator:=xlFilterValues
    Application.ScreenUpdating = True
End Sub

This is what I was hoping to get working, with a clear button that would clear the filters and clear the textbox, but it doesn't work:

Sub ClearSearch_Click()
Range("C2").Select
    Selection.ClearContents
    ActiveSheet.ListObjects("ScreenLog").Range.AutoFilter Field:=13
End Sub

Sub TextBox1_Change()
Application.ScreenUpdating = False
   
    ActiveSheet.ListObjects("ScreenLog").Range.AutoFilter Field:=13, Criteria1:="*" & [C2] & "*", Operator:=xlFilterValues
    Application.ScreenUpdating = True
End Sub

Here's my spreadsheet:

Screening Log.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3
4				Name			Mon/Yr of Birth
5		Screen ID	ID Number	First	Last	Initials	Month	Year	Acronym	Employee Name	Date of Approach	Agreed	If yes, Actual ID	ID as Text
6		S001	15387				1	5						15387
7		S002	5486745				2	5						5486745
8		S003	156				2	5						156
9		S004	158											158
10		S005	150045											150045
11		S006	4											4
12		S007	15264											15264
Sheet1

Cell Formulas
Range	Formula
N6:N12	N6	=LET(x, [@[ID Number]], IF(x=0, "", x))

 

[DanteAmor]

Try the following to filter numbers. Column N is no longer needed.

Sub TextBox1_Change()
  Dim tbl As ListObject
  Dim a, b()
  Dim i As Long, n As Long
  
  
  Application.ScreenUpdating = False
  
  Set tbl = ActiveSheet.ListObjects("ScreenLog")
  a = tbl.DataBodyRange
  
  For i = 1 To UBound(a, 1)
    If InStr(1, a(i, 2), TextBox1.Value) > 0 Then
      ReDim Preserve b(n)
      b(n) = CStr(a(i, 2))
      n = n + 1
    End If
  Next
  
  tbl.Range.AutoFilter Field:=2, Criteria1:=b, Operator:=xlFilterValues
  Application.ScreenUpdating = True
End Sub

Sub ClearSearch_Click()
  ActiveSheet.ListObjects("ScreenLog").Range.AutoFilter Field:=2
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[dancer5]

Thanks for looking into my issue!

For some reason it wouldn't work in my current workbook, the textbox and button weren't clickable, most likely something I did when trying to solve my issues but when I started a fresh workbook they worked.

The only remaining action would be when clicking the clear button, it doesn't clear the textbox, is this possible to do?

 

[DanteAmor]

it doesn't clear the textbox, is this possible to do?

You mean this:

Sub ClearSearch_Click()
  TextBox1.Value  = ""
  ActiveSheet.ListObjects("ScreenLog").Range.AutoFilter Field:=2
End Sub