Solved

Excel VBA: Real-Time Search or Filtering in Listbox

Posted on 2016-07-28
5
288 Views
Last Modified: 2016-08-27
I have a listbox with many records and I would like to have the ability to search inside the listbox.

The Listbox has several columns: Column 1 is the CustomerID, column 2 is the name and so on.

screengrab
The following code searches inside the listbox, but only in the first column - which is the ID.
How do I change the code so that it searches inside the second column?
Private Sub txtSearch_Change()
  Dim strText As String
  Dim i As Long
  
  strText = LCase(txtSearch.Text)
  With ListBox1
    For i = 0 To .ListCount - 1
      If LCase(Left$(.List(i), Len(strText))) = strText Then Exit For
    Next i
    If i = .ListCount Then
      ' No matching item was found, select nothing
      .ListIndex = -1
    Else
      ' A match was found, select it
      .ListIndex = i
    End If
  End With
End Sub

Open in new window


Is it also possible to filter the listbox as I type?

Thanks
example.xlsm
0
Comment
Question by:Massimo Scola
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41733032
Hi,

pls try

Private Sub txtSearch_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

  Dim strText As String
  Dim i As Long
  
  strText = LCase(txtSearch.Text)
  With ListBox1
    For i = 0 To .ListCount - 1
      If LCase(Left$(.List(i, 1), Len(strText))) = strText Or LCase(Left$(.List(i, 2), Len(strText))) = strText Then Exit For
    Next i
    If i = .ListCount Then
      ' No matching item was found, select nothing
      .ListIndex = -1
    Else
      ' A match was found, select it
      .ListIndex = i
    End If
  End With
End Sub

Open in new window

Regards
0
 

Author Comment

by:Massimo Scola
ID: 41733088
OK this works.  I have two questions

1. )Is there something like a wildcard operator which I can use if I only know part of the name?

2) If I want to filter the data, would I have to make use of arrays? Or what is the approach?
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 41733180
1) instead of Left use Instr
InStr(LCase(.List(i, 1)), strText)

Open in new window


2)then try
Private Sub txtSearch_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

Dim rw
Dim strText As String

strText = LCase(txtSearch.Text)
Dim rng As Range
Set rng = Range("Customers")
With ListBox1
    .RowSource = ""
    .ColumnCount = 3
    For Each rw In rng.Rows
        If InStr(LCase(Cells(rw.Row, 2)), strText) Or InStr(LCase(Cells(rw.Row, 3)), strText) Then
            .AddItem Cells(rw.Row, 1).Value
            .List(ListBox1.ListCount - 1, 1) = Cells(rw.Row, 2).Value
            .List(ListBox1.ListCount - 1, 2) = Cells(rw.Row, 3).Value
        End If
    Next
End With
End Sub

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question