Limit a Listbox
Data in Listboxes can get unruly causing the users to do a lot of scrolling. One way to rein them in is to allow the user to filter the Listbox by typing in a Textbox. It’s kind of like autocomplete. In this example, a Listbox is populated with all the customer names from the Northwind database’s Customers table. The user types in the Textbox and the Listbox is automatically filtered.
 |
 |
The Change event of the Textbox is used to filter the Listbox.
Dim i As Long
Dim sCrit As String
'Add asterisks around text for all matches
'UCase is used to make filter case-insensitive
sCrit = "*" & UCase(Me.tbxFind.Text) & "*"
With Me.lbxCustomers
'Start with a fresh list
.List = vaCustNames
'Loop through the list backward - always a good
'idea when you're deleting stuff
For i = .ListCount - 1 To 0 Step -1
'Remove the line if it doesn't match
'UCase used again here
If Not UCase(.List(i)) Like sCrit Then
.RemoveItem i
End If
Next i
End With
End Sub
I put the customer names in a variant array in the userform’s Initialize event. The array variable is a module level scope so I can use it anywhere in the userform’s module. It’s a little brute force to refresh the list with every change, but it wasn’t very slow for me and the 91 names in the list. If you had a much bigger list, you might track the length of the Textbox and only start fresh when the length goes down.
If Len(Me.tbxFind.Text) < lOldLen Then
'Start with a fresh list
.List = vaCustNames
End If
lOldLen = Len(Me.tbxFind.Text)
lOldLen is another module level variable that stores the previous length of the text in the Textbox. That may speed things up a bit with a large list.
Dick !
You beat me to a *very* similar post. Ok, I’ll put mine for tomorrow, showing a different method.
How about (Excel 2000+):
Private Sub tbxFind_Change()
lbxCustomers.List = Filter(vaCustNames, tbxFind.Text, True, vbCompareText)
End Sub
Dick, please remove this test!
OK, I guess that doesn’t work, for trying to put formatted code in a comment!
Stephen: 1. Filter is a great idea (smack!) 2. I will remove that test, but I’m going to leave it on there to remind me to see if I can get that to work in comments.
The Filter approach described by Stephen and Juan Pablo is more elegant, but it works for a one dimensional array only.
I just used a variation on Dick’s code above to whip off a customer lookup form with a six column listbox and six individual textboxes. Very slick.
- Jon
Dick,
I do this a different way. Not sure if it’s better or worse, but different. Since I’m already getting the data from a database with ADO, I just disconnect the recordset and use the Filter property to filter the data.
Maybe this is what Juan Pablo was going to suggest, but he’s had 2 weeks, so here goes.
Private mrsNames As ADODB.Recordset
Private Sub cmdOK_Click()
On Error Resume Next
mrsNames.Close
Set mrsNames = Nothing
On Error GoTo 0
End Sub
Private Sub txtName_Change()
FilterList txtName.Text
End Sub
Private Sub UserForm_Initialize()
Dim cn As ADODB.Connection
Dim cd As ADODB.Command
Set cn = New ADODB.Connection
With cn
.ConnectionString = “DSN=MYDSN;”
.CursorLocation = adUseClient
.Mode = adModeRead
.Open
End With
Set cd = New ADODB.Command
With cd
Set .ActiveConnection = cn
.CommandType = adCmdText
.CommandText = “SELECT p.ClientName AS [Full Name]” _
& ” FROM dbo.tblPayors p WITH (NOLOCK) ” _
& ” ORDER BY p.ClientName”
Set mrsNames = .Execute
End With
Set mrsNames.ActiveConnection = Nothing
FilterList “”
Set cd = Nothing
Set cn = Nothing
End Sub
Private Sub FilterList(rsFilter As String)
With lstNames
.Clear
mrsNames.Filter = “”
If Len(rsFilter) Then mrsNames.Filter = _
“[Full Name] LIKE ‘” & rsFilter & “%’”
If mrsNames.RecordCount Then .List = _
Application.Transpose(mrsNames.GetRows())
End With
End Sub
>> Maybe this is what Juan Pablo was going to suggest, but he’s had 2 weeks, so here goes.
Hey ! I did put that 2 weeks ago …
http://www.dicks-blog.com/archives/2005/02/17/limit-a-listbox-a-different-view/
>> Hey ! I did put that 2 weeks ago
My apologies, Juan Pablo! OK, so at least my method was different than yours.
Hi Dick,
Your post ‘Limit a Listbox’ is very interesting.
I was trying to use this for a functionality in excel,
it is giving me an error “Method, or Data Member not found” esply in the textbox area ‘With Me.lbxCustomers’. I might have done something wrong.
when I type in the textbox, ut stops with this error.
can you suggest what shud I do. Or is there anywhere you have the complete code for this example which I can take a look!
Thanks in advance.
Kris.
Hi Dick,
I have figured out the problem and fixed it. This works fine now. It is a good one.
Thanks
Kris.
Hi Dick, Kris and Jon,
Thanks for these posts. Very useful code. I’ve been trying to apply Dick’s code to limit the list in a 9 column list box with three different text fiels. I’m getting the same error “Method or Data member not found” when I try to debug the code. The error refers to the “.List =” part. Could anyone help me with this?
Sorry if this is a silly question.
Thanks!
Arnold.
Arnold: Send me your workbook if you can.
Dick,
I am trying to use this code in an access 2003 application so I don’t have a workbook. I’m sorry for not being clear about that. I have an access form with a listbox with 9 colums (list0). I’ve modified the code to point to Me.list0 and modified the other variable names but debugging fails on this line:
.List = sbj_name
Does this make sense?
Thanks for your help!
Arnold.
[...] they were functions in a “normal” programming language. From discussions such as Limit a Listbox in Dick Kusleika’s Daily Dose of Excel blog, it seems doing this directly in Excel can be [...]
Hi,
I a self taugh VBA ….
I cannot seem to get the above working…here is what I have so far:
Private Sub TextBox1_Change()
Dim i As Long
Dim sCrit As String
‘Add asterisks around text for all matches
‘UCase is used to make filter case-insensitive
sCrit = “*” & UCase(Me.TextBox1.Text) ‘& “*”
With Me.ListBox1
‘Start with a fresh list
.List = Application.Transpose(rcArray)
‘Loop through the list backward - always a good
‘idea when you’re deleting stuff
MsgBox .ListCount
For i = .ListCount - 1 To 0 Step -1
‘Remove the line if it doesn’t match
‘UCase used again here
If Not UCase(.List(i)) Like sCrit Then
.RemoveItem i
End If
Next i
End With
End Sub
Also, how would I get the value selected by cliking on the list box?
Thanks for any help,
Johnny
Hi,
how to set up this.
.List = vaCustNames
I have a range from A1:A10 and name as “vaCustNames” but i doesn’t work.
thank you.
Hi Dick,
Can you teach me how to do this in Excel.
Same example as in the above, assuming that the list range occupies the column A.
Hope you can help me about this.
Thanks.
Cesar:
At some point, like in your initialize event, you have to
Change ‘Sheet1′ to match your situation.
This is the one I modified, still not working. Pls be patient with me, I am new with this VBA.
Private Sub Textbox1_Change()
Dim i As Long
Dim sCrit As String
sCrit = “*” & UCase(Me.TextBox1.Text) & “*”
With Me.ListBox1
vaCustNames = Sheet1.Range(”A1:A10″).Value
.List = vaCustNames
For i = .ListCount - 1 To 0 Step -1
If Not UCase(.List(i)) Like sCrit Then
.RemoveItem i
End If
Next i
End With
End Sub
Another question:
What if i have 2 columns:
Column A is for Customer Codes
Column B is for Customer Names
Then, I want a Customer Search on Column B so that I may able to see its Customer Code (Column A) and the corresponding Customer Name (Column B).
Thanks for accomodating me.