Display All Matches from Search in Userform ListBox

Philip Treacy

August 20, 2020

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.

Microsoft Power BI Community Super User Logo

AUTHOR Philip Treacy Co-Founder / Owner at My Online Training Hub

Systems Engineer with 30+ years working for companies like Credit Suisse and E.D.S. in roles as varied as Network & Server Support, Team Leader and Consultant Project Manager.

These days Philip does a lot of programming in VBA for Excel, as well as PHP, JavaScript and HTML/CSS for web development.

He's particularly keen on Power Query where he writes a lot of M code.

When not writing blog posts or programming for My Online Training Hub, Philip can be found answering questions on the Microsoft Power BI Community forums where he is a Super User.

Import Data from a PDF to Excel

Import Data from a Picture to Excel Mobile

28 thoughts on “Display All Matches from Search in Userform ListBox”

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

    • 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

      • 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

    • 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

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

    • 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

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

    • 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

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

    • 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

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

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

    • 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

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

    • 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 Comment

Current ye@r *