Filter a list based on a text box value

brendalpzm

brendalpzm

Board Regular
Joined
Oct 3, 2022
Messages
59
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
Platform
  1. Windows
I created a list box with the following code:

VBA Code: 
Private Sub UserForm_Initialize()
Dim arrdata() As Variant
Dim lngIndex As Long
Dim lngRow As Long

    arrdata = Worksheets("Current").Range("A1").CurrentRegion.Value

    With Me.lstHeaders
        .ColumnCount = 7
        .ColumnWidths = "180,60,255,95,75,105,65"
        .Font.Size = 13
        .Font.Bold = True
        .Enabled = False
         .AddItem
        .List(lngRow, 0) = arrdata(1, 3)
        .List(lngRow, 1) = arrdata(1, 4)
        .List(lngRow, 2) = arrdata(1, 5)
        .List(lngRow, 3) = arrdata(1, 10)
        .List(lngRow, 4) = arrdata(1, 11)
        .List(lngRow, 5) = arrdata(1, 13)
        .List(lngRow, 6) = arrdata(1, 14)
    End With

    With Me.CarList
        .ColumnCount = 7
        .ColumnWidths = "180,60,255,95,75,105,65"
        .Font.Size = 13
    End With
    
    lngRow = 0
    
    For lngIndex = LBound(arrdata, 1) + 1 To UBound(arrdata, 1)
        If arrdata(lngIndex, 13) <> "completed" Then
            With Me.CarList
                .AddItem
                .List(lngRow, 0) = arrdata(lngIndex, 3)
                .List(lngRow, 1) = arrdata(lngIndex, 4)
                .List(lngRow, 2) = arrdata(lngIndex, 5)
                .List(lngRow, 3) = arrdata(lngIndex, 10)
                .List(lngRow, 4) = arrdata(lngIndex, 11)
                .List(lngRow, 5) = arrdata(lngIndex, 13)
                .List(lngRow, 6) = arrdata(lngIndex, 14)
            End With
            lngRow = lngRow + 1
        End If
    Next lngIndex
End Sub

and I created a text box that I want to use it as a search engine, One of the columns has full names, so I want to type the name in the text box and click a button to filter the table with the values that contain the typed values.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
For the filter we are also going to use the arrdata variable, but we are going to declare it in the General section.

Note: Change the 3 to the column number of the sheet where you have the name.
Rich (BB code): 
If arrdata(lngIndex, 13) <> "completed" And LCase(arrdata(lngIndex, 3)) Like "*" & LCase(TextBox1.Value) & "*" Then

Replace all your code with the following, the button is not necessary, the filter will be done automatically as you write in textbox1.
VBA Code: 
Option Explicit

Dim arrdata() As Variant        'at the beginning of all the code

Private Sub TextBox1_Change()
  Dim lngIndex As Long
  Dim lngRow As Long

  Me.CarList.Clear
  lngRow = 0
  For lngIndex = LBound(arrdata, 1) + 1 To UBound(arrdata, 1)
    If arrdata(lngIndex, 13) <> "completed" And LCase(arrdata(lngIndex, 3)) Like "*" & LCase(TextBox1.Value) & "*" Then
      Call add_ToListbox(lngRow, lngIndex)
      lngRow = lngRow + 1
    End If
  Next lngIndex
End Sub

Private Sub UserForm_Initialize()
  Dim lngIndex As Long
  Dim lngRow As Long
 
  arrdata = Worksheets("Current").Range("A1").CurrentRegion.Value
 
  With Me.lstHeaders
    .ColumnCount = 7
    .ColumnWidths = "180;60;255;95;75;105;65"     'I think they should be separated by semicolons, if not, change to commas
    .Font.Size = 13
    .Font.Bold = True
    .Enabled = False
     .AddItem
    .List(lngRow, 0) = arrdata(1, 3)
    .List(lngRow, 1) = arrdata(1, 4)
    .List(lngRow, 2) = arrdata(1, 5)
    .List(lngRow, 3) = arrdata(1, 10)
    .List(lngRow, 4) = arrdata(1, 11)
    .List(lngRow, 5) = arrdata(1, 13)
    .List(lngRow, 6) = arrdata(1, 14)
  End With
 
  With Me.CarList
    .ColumnCount = 7
    .ColumnWidths = "180;60;255;95;75;105;65"   'I think they should be separated by semicolons, if not, change to commas
    .Font.Size = 13
  End With
 
  lngRow = 0
  For lngIndex = LBound(arrdata, 1) + 1 To UBound(arrdata, 1)
    If arrdata(lngIndex, 13) <> "completed" Then
      Call add_ToListbox(lngRow, lngIndex)
      lngRow = lngRow + 1
    End If
  Next lngIndex
End Sub

Sub add_ToListbox(lngRow, lngIndex)
  With Me.CarList
    .AddItem
    .List(lngRow, 0) = arrdata(lngIndex, 3)
    .List(lngRow, 1) = arrdata(lngIndex, 4)
    .List(lngRow, 2) = arrdata(lngIndex, 5)
    .List(lngRow, 3) = arrdata(lngIndex, 10)
    .List(lngRow, 4) = arrdata(lngIndex, 11)
    .List(lngRow, 5) = arrdata(lngIndex, 13)
    .List(lngRow, 6) = arrdata(lngIndex, 14)
  End With
End Sub


--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------
 
Upvote 1
Solution
DanteAmor said:
For the filter we are also going to use the arrdata variable, but we are going to declare it in the General section.

Note: Change the 3 to the column number of the sheet where you have the name.
Rich (BB code): 
If arrdata(lngIndex, 13) <> "completed" And LCase(arrdata(lngIndex, 3)) Like "*" & LCase(TextBox1.Value) & "*" Then

Replace all your code with the following, the button is not necessary, the filter will be done automatically as you write in textbox1.
VBA Code: 
Option Explicit

Dim arrdata() As Variant        'at the beginning of all the code

Private Sub TextBox1_Change()
  Dim lngIndex As Long
  Dim lngRow As Long

  Me.CarList.Clear
  lngRow = 0
  For lngIndex = LBound(arrdata, 1) + 1 To UBound(arrdata, 1)
    If arrdata(lngIndex, 13) <> "completed" And LCase(arrdata(lngIndex, 3)) Like "*" & LCase(TextBox1.Value) & "*" Then
      Call add_ToListbox(lngRow, lngIndex)
      lngRow = lngRow + 1
    End If
  Next lngIndex
End Sub

Private Sub UserForm_Initialize()
  Dim lngIndex As Long
  Dim lngRow As Long
 
  arrdata = Worksheets("Current").Range("A1").CurrentRegion.Value
 
  With Me.lstHeaders
    .ColumnCount = 7
    .ColumnWidths = "180;60;255;95;75;105;65"     'I think they should be separated by semicolons, if not, change to commas
    .Font.Size = 13
    .Font.Bold = True
    .Enabled = False
     .AddItem
    .List(lngRow, 0) = arrdata(1, 3)
    .List(lngRow, 1) = arrdata(1, 4)
    .List(lngRow, 2) = arrdata(1, 5)
    .List(lngRow, 3) = arrdata(1, 10)
    .List(lngRow, 4) = arrdata(1, 11)
    .List(lngRow, 5) = arrdata(1, 13)
    .List(lngRow, 6) = arrdata(1, 14)
  End With
 
  With Me.CarList
    .ColumnCount = 7
    .ColumnWidths = "180;60;255;95;75;105;65"   'I think they should be separated by semicolons, if not, change to commas
    .Font.Size = 13
  End With
 
  lngRow = 0
  For lngIndex = LBound(arrdata, 1) + 1 To UBound(arrdata, 1)
    If arrdata(lngIndex, 13) <> "completed" Then
      Call add_ToListbox(lngRow, lngIndex)
      lngRow = lngRow + 1
    End If
  Next lngIndex
End Sub

Sub add_ToListbox(lngRow, lngIndex)
  With Me.CarList
    .AddItem
    .List(lngRow, 0) = arrdata(lngIndex, 3)
    .List(lngRow, 1) = arrdata(lngIndex, 4)
    .List(lngRow, 2) = arrdata(lngIndex, 5)
    .List(lngRow, 3) = arrdata(lngIndex, 10)
    .List(lngRow, 4) = arrdata(lngIndex, 11)
    .List(lngRow, 5) = arrdata(lngIndex, 13)
    .List(lngRow, 6) = arrdata(lngIndex, 14)
  End With
End Sub


--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------
Click to expand...
it brings up variable not defined error
 
Upvote 0
DanteAmor said:
For the filter we are also going to use the arrdata variable, but we are going to declare it in the General section.

Note: Change the 3 to the column number of the sheet where you have the name.
Rich (BB code): 
If arrdata(lngIndex, 13) <> "completed" And LCase(arrdata(lngIndex, 3)) Like "*" & LCase(TextBox1.Value) & "*" Then

Replace all your code with the following, the button is not necessary, the filter will be done automatically as you write in textbox1.
VBA Code: 
Option Explicit

Dim arrdata() As Variant        'at the beginning of all the code

Private Sub TextBox1_Change()
  Dim lngIndex As Long
  Dim lngRow As Long

  Me.CarList.Clear
  lngRow = 0
  For lngIndex = LBound(arrdata, 1) + 1 To UBound(arrdata, 1)
    If arrdata(lngIndex, 13) <> "completed" And LCase(arrdata(lngIndex, 3)) Like "*" & LCase(TextBox1.Value) & "*" Then
      Call add_ToListbox(lngRow, lngIndex)
      lngRow = lngRow + 1
    End If
  Next lngIndex
End Sub

Private Sub UserForm_Initialize()
  Dim lngIndex As Long
  Dim lngRow As Long
 
  arrdata = Worksheets("Current").Range("A1").CurrentRegion.Value
 
  With Me.lstHeaders
    .ColumnCount = 7
    .ColumnWidths = "180;60;255;95;75;105;65"     'I think they should be separated by semicolons, if not, change to commas
    .Font.Size = 13
    .Font.Bold = True
    .Enabled = False
     .AddItem
    .List(lngRow, 0) = arrdata(1, 3)
    .List(lngRow, 1) = arrdata(1, 4)
    .List(lngRow, 2) = arrdata(1, 5)
    .List(lngRow, 3) = arrdata(1, 10)
    .List(lngRow, 4) = arrdata(1, 11)
    .List(lngRow, 5) = arrdata(1, 13)
    .List(lngRow, 6) = arrdata(1, 14)
  End With
 
  With Me.CarList
    .ColumnCount = 7
    .ColumnWidths = "180;60;255;95;75;105;65"   'I think they should be separated by semicolons, if not, change to commas
    .Font.Size = 13
  End With
 
  lngRow = 0
  For lngIndex = LBound(arrdata, 1) + 1 To UBound(arrdata, 1)
    If arrdata(lngIndex, 13) <> "completed" Then
      Call add_ToListbox(lngRow, lngIndex)
      lngRow = lngRow + 1
    End If
  Next lngIndex
End Sub

Sub add_ToListbox(lngRow, lngIndex)
  With Me.CarList
    .AddItem
    .List(lngRow, 0) = arrdata(lngIndex, 3)
    .List(lngRow, 1) = arrdata(lngIndex, 4)
    .List(lngRow, 2) = arrdata(lngIndex, 5)
    .List(lngRow, 3) = arrdata(lngIndex, 10)
    .List(lngRow, 4) = arrdata(lngIndex, 11)
    .List(lngRow, 5) = arrdata(lngIndex, 13)
    .List(lngRow, 6) = arrdata(lngIndex, 14)
  End With
End Sub


--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------
Click to expand...
Nevermind It was my mistake, I didn't change the texbox1 to the actual name of the textbox, it solved my problem thanks!
 
Upvote 0
DanteAmor said:
For the filter we are also going to use the arrdata variable, but we are going to declare it in the General section.

Note: Change the 3 to the column number of the sheet where you have the name.
Rich (BB code): 
If arrdata(lngIndex, 13) <> "completed" And LCase(arrdata(lngIndex, 3)) Like "*" & LCase(TextBox1.Value) & "*" Then

Replace all your code with the following, the button is not necessary, the filter will be done automatically as you write in textbox1.
VBA Code: 
Option Explicit

Dim arrdata() As Variant        'at the beginning of all the code

Private Sub TextBox1_Change()
  Dim lngIndex As Long
  Dim lngRow As Long

  Me.CarList.Clear
  lngRow = 0
  For lngIndex = LBound(arrdata, 1) + 1 To UBound(arrdata, 1)
    If arrdata(lngIndex, 13) <> "completed" And LCase(arrdata(lngIndex, 3)) Like "*" & LCase(TextBox1.Value) & "*" Then
      Call add_ToListbox(lngRow, lngIndex)
      lngRow = lngRow + 1
    End If
  Next lngIndex
End Sub

Private Sub UserForm_Initialize()
  Dim lngIndex As Long
  Dim lngRow As Long
 
  arrdata = Worksheets("Current").Range("A1").CurrentRegion.Value
 
  With Me.lstHeaders
    .ColumnCount = 7
    .ColumnWidths = "180;60;255;95;75;105;65"     'I think they should be separated by semicolons, if not, change to commas
    .Font.Size = 13
    .Font.Bold = True
    .Enabled = False
     .AddItem
    .List(lngRow, 0) = arrdata(1, 3)
    .List(lngRow, 1) = arrdata(1, 4)
    .List(lngRow, 2) = arrdata(1, 5)
    .List(lngRow, 3) = arrdata(1, 10)
    .List(lngRow, 4) = arrdata(1, 11)
    .List(lngRow, 5) = arrdata(1, 13)
    .List(lngRow, 6) = arrdata(1, 14)
  End With
 
  With Me.CarList
    .ColumnCount = 7
    .ColumnWidths = "180;60;255;95;75;105;65"   'I think they should be separated by semicolons, if not, change to commas
    .Font.Size = 13
  End With
 
  lngRow = 0
  For lngIndex = LBound(arrdata, 1) + 1 To UBound(arrdata, 1)
    If arrdata(lngIndex, 13) <> "completed" Then
      Call add_ToListbox(lngRow, lngIndex)
      lngRow = lngRow + 1
    End If
  Next lngIndex
End Sub

Sub add_ToListbox(lngRow, lngIndex)
  With Me.CarList
    .AddItem
    .List(lngRow, 0) = arrdata(lngIndex, 3)
    .List(lngRow, 1) = arrdata(lngIndex, 4)
    .List(lngRow, 2) = arrdata(lngIndex, 5)
    .List(lngRow, 3) = arrdata(lngIndex, 10)
    .List(lngRow, 4) = arrdata(lngIndex, 11)
    .List(lngRow, 5) = arrdata(lngIndex, 13)
    .List(lngRow, 6) = arrdata(lngIndex, 14)
  End With
End Sub


--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------
Click to expand...
out of curiosity, what if I want to apply a 2nd filter from a combo box?
 
Upvote 0
Example:
Change this:
VBA Code: 
    If arrdata(lngIndex, 13) <> "completed" And LCase(arrdata(lngIndex, 3)) Like "*" & LCase(TextBox1.Value) & "*" Then

For this:
Rich (BB code): 
    If arrdata(lngIndex, 13) <> "completed" And LCase(arrdata(lngIndex, 3)) Like "*" & LCase(TextBox1.Value) & "*" And _ 
       LCase(arrdata(lngIndex, 5)) = Lcase(Combobox1.Value) Then

Adjust the values in red by your values.
 
Upvote 0
DanteAmor said:
Example:
Change this:
VBA Code: 
    If arrdata(lngIndex, 13) <> "completed" And LCase(arrdata(lngIndex, 3)) Like "*" & LCase(TextBox1.Value) & "*" Then

For this:
Rich (BB code): 
    If arrdata(lngIndex, 13) <> "completed" And LCase(arrdata(lngIndex, 3)) Like "*" & LCase(TextBox1.Value) & "*" And _
       LCase(arrdata(lngIndex, 5)) = Lcase(Combobox1.Value) Then

Adjust the values in red by your values.
Click to expand...
But when I write on the text box, since the combo box doesn't have any values yet the listbox is blank
 
Upvote 0
I thought it was simpler, but no.

Change your code to this:

VBA Code: 
Option Explicit

Dim arrdata() As Variant        'at the beginning of all the code

Private Sub ComboBox1_Change()
  Call Filter_Data
End Sub

Private Sub TextBox1_Change()
  Call Filter_Data
End Sub

Sub Filter_Data()
  Dim i As Long, lngRow As Long
  Dim tbox As String, cbox As String

  Me.CarList.Clear
  lngRow = 0
  For i = LBound(arrdata, 1) + 1 To UBound(arrdata, 1)
    If TextBox1.Value = "" Then tbox = LCase(arrdata(i, 3)) Else tbox = LCase(TextBox1.Value)
    If ComboBox1.Value = "" Then cbox = LCase(arrdata(i, 5)) Else cbox = LCase(ComboBox1.Value)
    
    If arrdata(i, 13) <> "completed" And LCase(arrdata(i, 3)) Like "*" & tbox & "*" And _
       LCase(arrdata(i, 5)) = cbox Then
      Call add_ToListbox(lngRow, i)
      lngRow = lngRow + 1
    End If
  Next
End Sub

Private Sub UserForm_Initialize()
  Dim lngIndex As Long
  Dim lngRow As Long
  
  arrdata = Worksheets("Current").Range("A1").CurrentRegion.Value
  
  With Me.lstHeaders
    .ColumnCount = 7
    .ColumnWidths = "180;60;255;95;75;105;65"     'I think they should be separated by semicolons, if not, change to commas
    .Font.Size = 13
    .Font.Bold = True
    .Enabled = False
     .AddItem
    .List(lngRow, 0) = arrdata(1, 3)
    .List(lngRow, 1) = arrdata(1, 4)
    .List(lngRow, 2) = arrdata(1, 5)
    .List(lngRow, 3) = arrdata(1, 10)
    .List(lngRow, 4) = arrdata(1, 11)
    .List(lngRow, 5) = arrdata(1, 13)
    .List(lngRow, 6) = arrdata(1, 14)
  End With
  
  With Me.CarList
    .ColumnCount = 7
    .ColumnWidths = "180;60;255;95;75;105;65"   'I think they should be separated by semicolons, if not, change to commas
    .Font.Size = 13
  End With
  
  lngRow = 0
  For lngIndex = LBound(arrdata, 1) + 1 To UBound(arrdata, 1)
    If arrdata(lngIndex, 13) <> "completed" Then
      Call add_ToListbox(lngRow, lngIndex)
      lngRow = lngRow + 1
    End If
  Next lngIndex
End Sub

Sub add_ToListbox(lngRow, lngIndex)
  With Me.CarList
    .AddItem
    .List(lngRow, 0) = arrdata(lngIndex, 3)
    .List(lngRow, 1) = arrdata(lngIndex, 4)
    .List(lngRow, 2) = arrdata(lngIndex, 5)
    .List(lngRow, 3) = arrdata(lngIndex, 10)
    .List(lngRow, 4) = arrdata(lngIndex, 11)
    .List(lngRow, 5) = arrdata(lngIndex, 13)
    .List(lngRow, 6) = arrdata(lngIndex, 14)
  End With
End Sub

You have to make adjustments to what is highlighted in red:
Rich (BB code): 
Sub Filter_Data()
  Dim i As Long, lngRow As Long
  Dim tbox As String, cbox As String

  Me.CarList.Clear
  lngRow = 0
  For i = LBound(arrdata, 1) + 1 To UBound(arrdata, 1)
    If TextBox1.Value = "" Then tbox = LCase(arrdata(i, 3)) Else tbox = LCase(TextBox1.Value)
    If ComboBox1.Value = "" Then cbox = LCase(arrdata(i, 5)) Else cbox = LCase(ComboBox1.Value)
    
    If arrdata(i, 13) <> "completed" And LCase(arrdata(i, 3)) Like "*" & tbox & "*" And _
       LCase(arrdata(i, 5)) = cbox Then
      Call add_ToListbox(lngRow, i)
      lngRow = lngRow + 1
    End If
  Next
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

brendalpzm
  • Locked
  • Question Question
Populate an ActiveX combo box according to a List Box values
Replies
1
Views
426
Fluff
Fluff
brendalpzm
  • Solved
Populate an ActiveX combo box according to a List Box values
Replies
3
Views
414
brendalpzm
brendalpzm
K
  • Solved
Previously working very complex VBA now stopped
Replies
7
Views
553
Alex Blakenburg
A
K
  • Solved
Using VBA to select a color coded cell
2
Replies
11
Views
960
Alex Blakenburg
A
K
  • Solved
Update to existing Move data macro
2
Replies
11
Views
745
Alex Blakenburg
A

Forum statistics

Threads
1,224,817
Messages
6,181,147
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top