My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home / Excel VBA / Display All Matches from Search in Userform ListBox
Display All Matches from Search in Userform ListBox
by

I've previously described how to use a form to search for a distinct value, like a unique ID.

This time I'm using the Find and FindNext methods to search a table for all results matching my search string.

You don't have to use a table, you can just as easily search a range.

Find/FindNext allow you to specify a text string and they will find all occurrences. So if you search for "Boo" that will match "Booth", "Boothroyd", "Booker" etc. In fact it will match any string with "boo" anywhere in it, like "Naboo".

image of search results

The way that Find/FindNext work is to search through a given range until the end of that range is reached. At this point, the search 'wraps around' to the start of the range and continues searching.

So to stop an infinite search loop the code must store the address of the first matching cell and then test each subsequent cell until it gets back to the first cell again.

Download the Workbook Including Userform and Code

Enter your email address below to download the workbook with the data and code from this post.

By submitting your email address you agree that we can email you our Excel newsletter.

The Source Data

I've created some dummy data in a table called Table1.

dummy data in table

Using a userform, I want to be able to search for someone by either their first name, last name, location or department.

I'll be returning the results, the entire matching row, into a ListBox in the userform.

You can extend the capability of the form by allowing it to modify or delete the records from the table. But for the purposes of this example, I'm just writing the search components.

The Search Userform

A simple form design with four text boxes where you can enter what you want to search for.

Results are listed in the central ListBox and the three buttons do exactly what they say they will.

Userform design

The Code

To start with, because I am restricting the search to only one thing at a time - you can't search for both first name and last name - when you enter something into one text box, all the others are cleared.

animated image showing search form in use

If you don't specify anything to search for, you'll get this friendly message.

error when no search term specified

After entering your search term, any matching records are listed.

animated image showing search and matching results displayed

If nothing matches, the form tells you so.

message nothing found

Summary

This is a standalone userform and code that can be adapted or inserted into your own project. Let me know via the forum if you have any questions or issues using the sample workbook that you can download at the top of this post.

Display All Matches from Search in Userform ListBox

More Userforms Posts

dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Hide, Delete, Sort Sheets with VBA in Excel
Using a form and some VBA, you can quickly hide, unhide, delete and sort sheets in your Excel workbooks.
add data to combo box drop down list in excel form
If the values in your Excel Form's drop down list aren't sufficient, allow the user to add their own, and save the new value in the list to use again.
Excel forms, insert, update, delete data
Insert, update and delete data in an Excel table with a user form.
excel form data validation
Thorough and effective Excel form data validation techniques. Clearly communicate errors to your users without annoying them.
excel date picker
An Excel date picker (calendar) for all versions of Excel, both 32 and 64 bit. An easy to use VBA class, with examples to download in a workbook
excel userforms
Learn how to create Excel forms for tasks like data entry. Use form controls to gather data, then enter this data into a table on your worksheet.
Excel Form Controls
Add interactivity to your spreadsheets with user friendly form controls like option buttons, combo boxes, check boxes and scrollbars.

More Excel VBA Posts

Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts
Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel
Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list
Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category: Tag:

Reader Interactions

Comments

  1. BeePee

    Hi. I liked the look of this and thought it could be very useful in a Gardening Almanac that I am creating. However, having created my form and cribbing your code (my first attempt at user forms) copying almost parrot fashion, I got adisapointment when it failed to work.

    The form displays ok but if and the Close button works. But when I compile the code I get the ‘Variable not defined’ pop-up and “Results” highlighted in the first Case statement. Icannot see what I have missed. Can someone point me in the right direction please.
    Thank you in anticipation
    BeePee

    Reply

    • BeePee

      Hi It’s BeePee — I have solved the above error – me being stupid!!! First attempt hey ho!
      BUT I now get an error highlighting “.Clear” (again first Case statement) and displaying “Method or data member not found”.

      Again any help appreciated, please.
      BeePee

      Reply

      • Philip Treacy

        Hi,

        Without seeing your code it’s almost impossible to debug it.

        Please start a topic on our forum and attach your file.

        Regards

        Phil

        Reply

    • Catalin Bombea

      Thank you for your feedback Slotus, appreciate it!
      Feel free please to use our forum as well, if you ever need a hand on excel or vba userforms.
      Cheers,
      Catalin

      Reply

  3. Steve du Plessis

    Thank you for this great tutorial. It has been awesome for an absolute beginner like me. I have adapted it to search through my table and return the data expected in the results box.

    I would like to allow a user to select the record they want from the results box and allow them to hit one of two buttons “Active” and “Inactive” to change a status value in a column for that particular entry. I cannot work out how to get the row number of the selection to allow this action to be performed. Can you help me work out what record is highlighted from the result box?

    Reply

    • Philip Treacy

      Hi Steve,

      The most straightforward way I can think to do this is to have a an identified for each row/record, and to read/load that into your form.

      When a result is selected you can then use a loop like this to identify which row in the results is selected

      For i = 0 To Results.ListCount – 1
      If Results.Selected(i) Then
      SelectedRow = i
      End If
      Next

      Then read the unique id for that row and you can then find that row in the sheet and write back any change you want.

      If you’re getting stuck, please start a topic on our forum and attach your workbook.

      Regards

      Phil

      Reply

  4. Gael

    Hello, thanks for this VBA, I really like this VBA, but this can be a little further? like, if I want to search with the another(s) textboxes at the same time, I need to be more specific when I look for some values.

    Thanks in advance.

    Have a nice day 😀

    Reply

    • Philip Treacy

      Thanks Gael.

      I’m not totally clear on what you want. Please start a topic on our forum, attach a sample file with data, and an explanation of what it is you are trying to achieve.

      Regards

      Phil

      Reply

  5. Zacky

    Sorry im just a beginner, can I use this form and edit the “Search Form”? How can I change the First Name to (example) Ticket Number list label?

    Reply

    • Philip Treacy

      Hi Zacky,

      Sorry I’m not clear on what you want to do. Change First Name to Ticket Number list label?

      Maybe if you post a question on our forum with some example data/workbook it’ll be clearer.

      Regards

      Phil

      Reply

  6. Stuart Allison

    Hi,

    I’ve changed your code about a little to add more search options and rows, but it is only returning results from the first column (FirstName in your case) rather than the whole row.

    What do i need to change to include more rows in the results? I thought it would be:

    Results.AddItem
    Results.List(RowCount, 0) = FirstCell(1, 1)
    Results.List(RowCount, 1) = FirstCell(1, 2)
    Results.List(RowCount, 2) = FirstCell(1, 3)
    Results.List(RowCount, 3) = FirstCell(1, 4)
    Results.List(RowCount, 4) = FirstCell(1, 5)
    Results.List(RowCount, 5) = FirstCell(1, 6)
    Results.List(RowCount, 6) = FirstCell(1, 7)
    RowCount = RowCount + 1

    However this is still returning the first column only

    Thanks,
    Stuart

    Reply

      • Catalin Bombea

        Can you upload a test file on our forum so we can see what’s wrong?
        Here is a link, create a new topic to upload.

        Reply

    • Jesse

      Hi Stuart, did you find a resolution for this? I am getting the same problem, only returning results in the first column.

      Thanks,
      Jesse

      Reply

      • Catalin Bombea

        Hi Jesse,
        Can you upload a test file on our forum so we can see what’s wrong?
        Here is a link, create a new topic to upload.

        Reply

  8. David Lewis

    Thankyou for this code Phillip. It is helping me to improve my understanding of user forms and coding search terms.
    In my case I’ve adapted this to search through a list of around 1,500 clinicians in a table to locate any possible matches prior to adding a new clinician (that’s the next step).
    In my results on the form I’m only having the search term returned, not the whole row. I suspect it is most likley due to the structure of my data, the first name/surname are in columns 3 & 4.
    Must I include the first 2 columns as part of the search form & terms?
    I will try to post this in the forum questions if I can find the correct thread.
    Thanks again.

    Reply

    • Philip Treacy

      Hi,

      I don’t really understand your question. Yes, you can use PQ to do web scraping but what do you mean by ‘a website that has a search engine’?

      Phil

      Reply

  10. Matt

    Hi, How would I tweak the code to use all the search boxes as search terms, For Example: I type a first name and a location to search for but if only one matches the result doesn’t show,

    I have tweaked it so it doesn’t clear the boxes when you type into another one but it still only shows results based on the last search box so if I search for a first name and location the results are solely based on the location irrelevant to whether the first name matches my search term,

    Thanks

    Reply

    • Catalin Bombea

      Hi Matt,
      Can you post please a sample file on our forum?
      Will be much easier to help you make it work.
      Cheers,
      Catalin

      Reply

    • Catalin Bombea

      Hi Joshua
      You can put a button that calls the form in any sheet you like, the form does not depend to a specific sheet.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *