Limit a Listbox

Posted on by Dick Kusleika

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.

LimitList1 LimitList2

The Change event of the Textbox is used to filter the Listbox.

Private Sub tbxFind_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.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.

    With Me.lbxCustomers
        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.

Posted in Uncategorized

21 thoughts on “Limit a Listbox”

  1. Dick !

    You beat me to a *very* similar post. Ok, I’ll put mine for tomorrow, showing a different method.

  5. 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.

  6. 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

  7. 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

  10. 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.

  11. Hi Dick,
    I have figured out the problem and fixed it. This works fine now. It is a good one.
    Thanks
    Kris.

  12. 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.

  14. 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.

  16. 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

  17. 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.

  18. 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.

  19. Cesar:

    At some point, like in your initialize event, you have to

    vaCustNames = Sheet1.Range(“A1:A10”).Value

    Change ‘Sheet1’ to match your situation.

  20. 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

  21. 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.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.