Multi column listbox and textbox search

Anthony86

Board Regular
Joined
Jan 31, 2018
Messages
62
Office Version
  1. 365
Platform
  1. Windows
Hi Guys,

I've got a user form with a listbox and textbox.
What I'm trying to achieve is when I type things in the textbox it searches the listbox and removes anything that doesnt relate.("*" Wildcard "*") The column I want it to search in is column C and it currently has 6 columns. What is the fastest way to achieve this without taking to much of a hit on processing?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi,

how are you populating the listbox?

Are you using column headers?
 
Upvote 0
Hi, I'm not using column headers as I've designed labels as the headers.
Code:
Private Sub UserForm_Initialize()ListBox1.ColumnCount = 6
ListBox1.ColumnWidths = "80,80,245,80,78"
ListBox1.RowSource = "DataList"


End Sub
 
Upvote 0
I was using this snippet of code to search on a smaller database, but now I have a really big database the code isn't working.

Code:
Dim X, i As Long, ii As Long, iii As Integer    X = [DataList]
    
    With ListBox1
        If TextBox1 = "" Then
            .RowSource = "DataList"
        Else
            .RowSource = ""
            For i = 1 To UBound(X, 1)
                If LCase(X(i, 3)) Like LCase(TextBox1) & "*" Then
                    For ii = 1 To 6
                        .AddItem
                        .List(iii, ii - 1) = X(i, ii)
                    Next
                    iii = iii + 1
                End If
            Next
        End If
    End With
 
Upvote 0
Try this
For a base of 20,000 records it takes less than a second for every letter you enter in the textbox.

Code:
Private Sub TextBox1_Change()
  Dim a, sh As Worksheet, i As Long, j As Long
  ListBox1.Clear
  Set sh = Sheets("Sheet1")
  a = sh.Range("A2:F" & sh.Range("C" & Rows.Count).End(xlUp).Row)
  j = 1
  ReDim b(1 To UBound(a), 1 To 6)
  For i = 1 To UBound(a)
    If a(i, 3) Like "*" & TextBox1.Value & "*" Then
      b(j, 1) = a(i, 1)
      b(j, 2) = a(i, 2)
      b(j, 3) = a(i, 3)
      b(j, 4) = a(i, 4)
      b(j, 5) = a(i, 5)
      b(j, 6) = a(i, 6)
      j = j + 1
    End If
  Next
  ListBox1.List = b()
End Sub
 
Upvote 0
Try this
For a base of 20,000 records it takes less than a second for every letter you enter in the textbox.

Code:
Private Sub TextBox1_Change()
  Dim a, sh As Worksheet, i As Long, j As Long
  ListBox1.Clear
  Set sh = Sheets("Sheet1")
  a = sh.Range("A2:F" & sh.Range("C" & Rows.Count).End(xlUp).Row)
  j = 1
  ReDim b(1 To UBound(a), 1 To 6)
  For i = 1 To UBound(a)
    If a(i, 3) Like "*" & TextBox1.Value & "*" Then
      b(j, 1) = a(i, 1)
      b(j, 2) = a(i, 2)
      b(j, 3) = a(i, 3)
      b(j, 4) = a(i, 4)
      b(j, 5) = a(i, 5)
      b(j, 6) = a(i, 6)
      j = j + 1
    End If
  Next
  ListBox1.List = b()
End Sub

This works great, and is really fast. Thank you
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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