Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Course Of The Month
3 days, 13 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Excel VBA: Real-Time Search or Filtering in Listbox
Posted on 2016-07-28
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.
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?
Is it also possible to filter the listbox as I type?
Thanks
example.xlsm
The Listbox has several columns: Column 1 is the CustomerID, column 2 is the name and so on.
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
Is it also possible to filter the listbox as I type?
Thanks
example.xlsm
[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
Active today
Hi,
pls try
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
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?
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?
Active today
1) instead of Left use Instr
2)then try
InStr(LCase(.List(i, 1)), strText)
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Outlook for dependable use in a very small business
This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month3 days, 13 hours left to enroll
630 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.