Listing Data in a ListBox Challenge


New Member
Jun 19, 2012
Hi Guys,

I have a form with 4 TextBoxe's "TexBox1,2,3 etc" and a ListBox1 were the whole idea is that you enter a value in TexBox1 and the VBA code looks for any and all items on your data "respective worksheet" and lists any/all data that it finds "corresponding to the value in TextBox 1" in the ListBox1, just like a database of sorts. Also note the other TextBoxe's 2,3,4 have info in them as well corresponding to the value you put in TextBox1 using the offset function.

Please note: The code I have WORKS for the condition above
but what I am having problems with is that I want it to work for all the TextBoxes respectively!

What I mean by this is that when ever I enter data in TextBox 1 or 2 or 3 or 4 that the results will be listed in the ListBox1. The reason I need this is, if the info I require for TextBox1 isn't available I can search for the info using the other criteria in the other TextBoxes.

I have attached the code I have already so please feel free to modify it. If you require the test worksheet please let me know and I will email it on to you, this might be better to give you an idea to see it working.

Really could use your help guys??

PS: forgot to mention that when the code finds entries that relate to TextBox1 it comes up with a message window with the number of entries it found... just in case you were wondering about the code at the bottom of the cmdFind method.

Dim MyData     As Range
Dim c          As Range
Dim rFound     As Range
Dim r          As Long
Dim rng        As Range

Dim oCtrl      As MSForms.Control

Private Sub cmbFind_Click()
    Dim strFind As String    'what to find
    Dim FirstAddress As String
    Dim rSearch As Range  'range to search
    Set rSearch = Sheet1.Range("a6", Range("a65536").End(xlUp))
    Dim f      As Integer

    strFind = Me.TextBox1.Value    'what to look for

    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            With Me    'load entry to form
                .TextBox2.Value = c.Offset(0, 1).Value
                .TextBox3.Value = c.Offset(0, 2).Value
                .TextBox4.Value = c.Offset(0, 3).Value
                f = 0
            End With
            FirstAddress = c.Address
                f = f + 1    'count number of matching records
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
            If f > 1 Then
                Select Case MsgBox("There are " & f & " instances of " & strFind, vbOKCancel Or vbExclamation Or vbDefaultButton1, "Multiple entries")

                    Case vbOK
                    Case vbCancel
                        'do nothing
                End Select

            End If
        Else: MsgBox strFind & " not listed"    'search failed
        End If
    End With
    If Sheet1.AutoFilterMode Then Sheet1.Range("A8").AutoFilter

End Sub

Sub FindAll()
    Dim strFind As String    'what to find
    Dim rFilter As Range     'range to search
    Set rFilter = Sheet1.Range("a8", Range("d65536").End(xlUp))
    Set rng = Sheet1.Range("a7", Range("a65536").End(xlUp))
    strFind = Me.TextBox1.Value
    With Sheet1
        If Not .AutoFilterMode Then .Range("A8").AutoFilter
        rFilter.AutoFilter Field:=1, Criteria1:=strFind
        Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
        For Each c In rng
            With Me.ListBox1
                .AddItem c.Value
                .List(.ListCount - 1, 1) = c.Offset(0, 1).Value
                .List(.ListCount - 1, 2) = c.Offset(0, 2).Value
                .List(.ListCount - 1, 3) = c.Offset(0, 3).Value
                .List(.ListCount - 1, 4) = c.Offset(0, 4).Value
            End With
        Next c
    End With
End Sub

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Have you considered using the Autofilter feature? You would have dropdown lists in each column of the header row. You select the item you want to filter on from one or of the dropdown list and only those records would be shown.

Video: Filter data by using an AutoFilter

The Autofilter feature could be used in VBA to populate your Listbox instead of the .Find method. Or you may not need your UserForm as it duplicates what Autofilter does on the worksheet.
Last edited:
Upvote 0
Thanks so much for your reply,

Unfortunitly the AutoFilter is not what I was looking for! I am looking for VBA Code in addition to what I have attached for my userform to work with multiple Textboxes!
The code I attached is just a very small part of the overall large program I am trying to develop, Just stuck on figuring out how to search with multiple TextBoxes.

Thanks again for the effort though!


Upvote 0
I would still recommend using Autofilter. Try something like this (I made a few assumptions).

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] cmbFind_Click()

    [COLOR=darkblue]Dim[/COLOR] rSearch [COLOR=darkblue]As[/COLOR] Range        [COLOR=green]'range to search[/COLOR]
    [COLOR=darkblue]Set[/COLOR] rSearch = Sheet1.Range("A5", Range("A65536").End(xlUp)).Resize(, 5)
    rSearch.AutoFilter    [COLOR=green]'Clear previous filter if any[/COLOR]
    [COLOR=darkblue]If[/COLOR] Len(TextBox1.Value) [COLOR=darkblue]Then[/COLOR] rSearch.AutoFilter Field:=1, Criteria1:=TextBox1.Value
    [COLOR=darkblue]If[/COLOR] Len(TextBox2.Value) [COLOR=darkblue]Then[/COLOR] rSearch.AutoFilter Field:=2, Criteria1:=TextBox2.Value
    [COLOR=darkblue]If[/COLOR] Len(TextBox3.Value) [COLOR=darkblue]Then[/COLOR] rSearch.AutoFilter Field:=3, Criteria1:=TextBox3.Value
    [COLOR=darkblue]If[/COLOR] Len(TextBox4.Value) [COLOR=darkblue]Then[/COLOR] rSearch.AutoFilter Field:=4, Criteria1:=TextBox4.Value
    [COLOR=darkblue]If[/COLOR] Sheet1.Range("A65536").End(xlUp).Row > 5 [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]With[/COLOR] Me.ListBox1
            [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] c [COLOR=darkblue]In[/COLOR] rSearch.Offset(1).Resize(, 1).SpecialCells(xlCellTypeVisible)
                .AddItem c.Value
                .List(.ListCount - 1, 1) = c.Offset(0, 1).Value
                .List(.ListCount - 1, 2) = c.Offset(0, 2).Value
                .List(.ListCount - 1, 3) = c.Offset(0, 3).Value
                .List(.ListCount - 1, 4) = c.Offset(0, 4).Value
            [COLOR=darkblue]Next[/COLOR] c
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
            MsgBox "There are " & rSearch.Resize(, 1).SpecialCells(xlCellTypeVisible).Count - 1 & _
                   " records found. ", vbExclamation, "Multiple entries"
        [COLOR=darkblue]If[/COLOR] MsgBox("No matching records. " & vbCr & "Clear the search fields? ", vbYesNo, "No Records Found") = vbYes [COLOR=darkblue]Then[/COLOR]   [COLOR=green]'search failed[/COLOR]
            TextBox1.Value = ""
            TextBox2.Value = ""
            TextBox3.Value = ""
            TextBox4.Value = ""
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    Sheet1.AutoFilterMode = [COLOR=darkblue]False[/COLOR]

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
Upvote 0
Sorry for the delay in getting back to you!
Thanks a mill for the code, I will try it out and let you know how I get on!
Upvote 0
Hey AlphaFrog,

Just retrofitting your code into mine, super job Man! It worked a dream!
Thanks for all the help! You always come up trumps for me.

Thanks again!

PS: is there a rating system on MrExcel, if so you have 5 stars from me boyo!!
Upvote 0

Forum statistics

Latest member

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