• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

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

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Display All Matches from Search in Userform ListBox

You are here: Home / Excel VBA / Display All Matches from Search in Userform ListBox
Display All Matches from Search in Userform ListBox
August 20, 2020 by Philip Treacy

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.
Please enter a valid email address.

Download the Excel Workbook.

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

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

Multi-Language Excel Calendar (Date Picker) for 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

Searching for Data With a User Form

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

Hide, Delete and Sort Sheets With VBA Userform

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

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

Excel Forms – Insert, Update and Delete

Insert, update and delete data in an Excel table with a user form.
excel form data validation

Excel Form Data Validation

Thorough and effective Excel form data validation techniques. Clearly communicate errors to your users without annoying them.
excel date picker

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

Excel Forms

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

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

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

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

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

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

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

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

Multi-Language Excel Calendar (Date Picker) for 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

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

Searching for Data With a User Form

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

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: Excel VBATag: Userforms
Previous Post:Import Data from a PDF to ExcelImport Data from a PDF to Excel
Next Post:Import Data from a Picture to Excel Mobiledata from picture icon

Reader Interactions

Comments

  1. BeePee

    March 21, 2022 at 4:27 am

    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

      March 21, 2022 at 4:48 am

      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

        March 21, 2022 at 2:30 pm

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

    December 17, 2021 at 3:34 am

    I have been looking for an elegant solution to this for a while. Thank you very much!

    Reply
    • Catalin Bombea

      December 18, 2021 at 2:17 pm

      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

    August 13, 2021 at 3:01 am

    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

      August 17, 2021 at 8:20 pm

      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

    July 14, 2021 at 5:52 am

    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

      July 14, 2021 at 1:14 pm

      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

    June 20, 2021 at 3:19 am

    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

      June 21, 2021 at 3:51 pm

      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

    March 4, 2021 at 7:32 pm

    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

      March 4, 2021 at 8:13 pm

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

      Reply
    • Jesse

      July 5, 2021 at 5:47 pm

      Hi Stuart,

      Did you get an answer to this? I am experiencing the same issue

      Thanks,
      Jesse

      Reply
      • Catalin Bombea

        July 7, 2021 at 12:55 am

        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

      July 8, 2021 at 4:02 pm

      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

        July 9, 2021 at 3:53 pm

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

    February 27, 2021 at 4:43 am

    Very good very nice

    Reply
    • Philip Treacy

      March 3, 2021 at 1:53 pm

      Thanks

      Reply
  8. David Lewis

    January 5, 2021 at 9:00 am

    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
    • Catalin Bombea

      January 6, 2021 at 5:37 am

      Hi David,
      Should be in https://www.myonlinetraininghub.com/excel-forum/vba-macros

      Reply
  9. Swap123

    November 23, 2020 at 3:16 pm

    Is it possible to scrape data using excel power query from a website which has a search engine?

    Reply
    • Philip Treacy

      November 23, 2020 at 7:03 pm

      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

    November 3, 2020 at 10:25 pm

    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

      November 5, 2020 at 10:02 pm

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

    October 12, 2020 at 11:43 am

    Can you make this search from another sheet so the search form will be on a different sheet?

    Reply
    • Catalin Bombea

      October 12, 2020 at 1:13 pm

      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 Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.