how to add textbox as search for listbox?

[raz355]

how can I add textbox as search to this code. I need it to search for a single column for different textbox, for example textbox1 search in column C, textbox2 search in column E, and textbox3 search in column K

Private Sub txt_sn1_Change()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Dim r As Integer
    Set Sh = Worksheets("Systemtest")
    With Sh
        Set Rng = .Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
    End With

      ReDim b(1 To 1, 1 To 11)  '
      With ListBox1
        .ColumnCount = 11       '
        .List = b               '
        .Clear                  '
      End With
    '
    r = 0
    For Each Cell In Rng
        With Cell
            ListBox1.AddItem .Value
            ListBox1.List(r, 1) = .Offset(0, 1).Value
            ListBox1.List(r, 2) = .Offset(0, 2).Value
            ListBox1.List(r, 3) = .Offset(0, 3).Value
            ListBox1.List(r, 4) = .Offset(0, 4).Value
            ListBox1.List(r, 5) = .Offset(0, 5).Value
            ListBox1.List(r, 6) = .Offset(0, 6).Value
            ListBox1.List(r, 7) = .Offset(0, 7).Value
            ListBox1.List(r, 8) = .Offset(0, 8).Value
            ListBox1.List(r, 9) = .Offset(0, 9).Value
            ListBox1.List(r, 10) = .Offset(0, 10).Value
          End With
        r = r + 1
    Next Cell
End Sub

 

[DanteAmor]

Hi and welcome to MrExcel!

For these types of searches, I show you an alternative. Replace all your code with the following code.

Change in the code in these lines the names of the textboxes (in red, green and blue colors) by the names of your textboxes

    If txt_sn1 = "" Then txt1 = a(i, 3) Else txt1 = txt_sn1     'textbox1 search in C
    If txt_sn2 = "" Then txt2 = a(i, 5) Else txt2 = txt_sn2     'textbox2 search in E
    If txt_sn3 = "" Then txt3 = a(i, 11) Else txt3 = txt_sn3    'textbox3 search in K

The code loads in an array 'a' all the information from the sheet. Every time you enter data in any of the 3 textboxes, it automatically performs a filter of the information, but in memory, it passes the data that matches to array 'b' and then loads listbox1 with the data from array 'b' .

Maybe it is necessary to make some adjustment in the search, since I am only considering that columns C, E and K have texts, but if it has numbers or dates then the type of search will have to be adjusted.

Test the code with text data to get familiar with searching, and then we'll make adjustments as needed.

Option Explicit     'These lines at the beginning of all the code
Dim a As Variant

Private Sub txt_sn1_Change()   'Fit to the name of your textbox
  Call Filter_Data
End Sub
Private Sub txt_sn2_Change() 'Fit to the name of your textbox
  Call Filter_Data
End Sub
Private Sub txt_sn3_Change() 'Fit to the name of your textbox
  Call Filter_Data
End Sub

Sub Filter_Data()
  Dim i As Long, j As Long, k As Long
  Dim txt1 As String, txt2 As String, txt3 As String
  Dim b As Variant
 
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  For i = 1 To UBound(a, 1)
    If txt_sn1 = "" Then txt1 = a(i, 3) Else txt1 = txt_sn1     'textbox1 search in C
    If txt_sn2 = "" Then txt2 = a(i, 5) Else txt2 = txt_sn2     'textbox2 search in E
    If txt_sn3 = "" Then txt3 = a(i, 11) Else txt3 = txt_sn3    'textbox3 search in K
  
    'search: textbox1 in C, textbox2 in E, and textbox3 in K
    If LCase(a(i, 3)) Like "*" & LCase(txt1) & "*" And _
       LCase(a(i, 5)) Like "*" & LCase(txt2) & "*" And _
       LCase(a(i, 11)) Like "*" & LCase(txt3) & "*" Then
      k = k + 1
      For j = 1 To UBound(a, 2)
        b(k, j) = a(i, j)
      Next
    End If
  Next
  ListBox1.List = b
End Sub

Private Sub UserForm_Activate()
  Dim sh As Worksheet
  Set sh = Worksheets("Systemtest")
  a = sh.Range("A2:K" & sh.Range("A" & Rows.Count).End(xlUp).Row).Value
  With ListBox1
    .ColumnCount = UBound(a, 2)
    .List = a
  End With
End Sub

 

[raz355]

Its exactly how I need it to be, thanks alot for the help. Btw is there anyway i can show the header in row A1. I try to change sh.range("A1:K....... But when I start type in textbox its disappear and show A2 and so on.

 

[raz355]

Also can you show me how the code is if the case were for date or integer(for learning purposes)