• 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

Searching for Data With a User Form

You are here: Home / Excel VBA / Searching for Data With a User Form
search for data with userform
January 28, 2020 by Philip Treacy

I was recently asked to provide the ability to search a list of records (like a table) using a user form, and then populate the fields of the form when the record was found.

The data can then be checked or modified before saving back to the workbook.

This post looks at how to create such a form.

Your browser does not support the video tag.

Related Posts: This is part of a series of posts I've written about Excel Forms. The other posts might interest you.

Excel Forms Create an Excel user form

Excel Date Picker (Calendar) Use a VBA class to implement a date picker. Sample code and examples provided. A VBA class is better than an ActiveX control in that it doesn't require anyone who uses your workbook to install additional controls or software.

Excel Form Data Validation Check the data being entered in your form is valid, and inform the user about errors

Excel Forms : Insert, Update and Delete Data Using your Excel Form to insert, update and delete records in a table.


To start, I've created some dummy data in a table

Table of dummy data for searching

The form obviously needs fields for each column so the basic form design looks like this

search Userform design

The 'Not found' is a label that will be used to indicate when a record isn't found. To start with this label is not visible. When a record isn't found the code just makes it visible.

The macro to load the form is run by clicking a shape in the worksheet.

Searching with the Form

With the userform displayed we can enter a number into the record field and click on Search.

searching for record with user form

The code now has to locate this number in the Record column of the table and then populate the form's fields with the data from that found record.

If the record number is not found, the 'Not found' error is displayed.

Download Sample Workbook

Enter your email address below to download the workbook containing all the 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.

All the code in this post can be downloaded in this workbook.

Download the Excel Workbook. Note: This is a .xlsm file please ensure your browser doesn't change the file extension on download.

Form VBA for Searching

Using the Match function the code can locate the row within the table where the Record Number is.

The value entered into the form is a string so it must be cast to a Long using CLng(). I'm using structured references to search only the data in the Record column. The table is just called the default name, Table1.

If I'm looking for Record Number 47, which is the value in TextBox1, Match uses this to return the number of the table row where 47 is located.

VBA Match looking for data in table

I now know where the record is so I can set a range to the first cell of that record

Set range to first cell in table row

Error Handling

When the code searches for the record number an error can occur if that number is not found, so I turn off default error handling before doing this search.

Once the search if performed I can test if an error has occurred and display the 'Not found' error message.

Populating the Form With the Record's Data

Knowing the first cell of the record I can use Offset to read the values from each field and put them into the form.

VBA to populate the user form fields

Populated user form

Summary

This is a standalone piece of code for searching and can be adapted to many situations. Try integrating it with your own code or modify the user form code from my other blog posts.

search for data with userform

More Userforms 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.
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.
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:list_first_monday_date_in_each_monthList First Monday Date in Each Month
Next Post:Cross Highlight Excel Charts

Reader Interactions

Comments

  1. Keith Barnes

    June 20, 2022 at 7:21 pm

    Hi

    Great site and many thanks for the free downloads.

    Could you however help with a userform that i am attemting to put together.

    Instead of the searchbox aquiring specific table data from the table, can the same data be provided from a combo drop down box.

    Any help will be really appreciated.

    Thanks in advance

    Keith

    Reply
    • Mynda Treacy

      June 20, 2022 at 8:26 pm

      Hi Keith,

      Please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  2. Carlo James

    November 8, 2021 at 7:24 pm

    Hello first of all thank you for this great work. Im new on VBA how can i change the record search number length? my search record is between 5 to 13 digits

    Reply
    • Catalin Bombea

      November 12, 2021 at 7:21 pm

      Hi Carlo,
      Most probably, there will be more than 1 record matching that number of digits, the form is designed to show one only.
      You have to iterate through all records, to check the number of digits, also the form is not a good fit, you have to load multiple possible results in a listbox.
      Try our forum, start a new topic and will try to help you there, you’ll be able to upload there a sample file.
      Cheers,
      Catalin

      Reply
  3. Md. Jamal Matubber

    May 2, 2021 at 2:08 am

    I would be grateful to you if you kindly send me the information that I am using a vba macro enabled database where I have found that some cells data I find but after some cells does not show in a sheet form by searching a rectangular command now what can I do for finding the others.Please, Help me.

    Reply
    • Philip Treacy

      May 4, 2021 at 9:21 am

      Hi,

      Sorry I don’t clearly understand the problem. Can you please start a topic on our forum and attach your file with your explanation.

      regards

      Phil

      Reply
  4. Nicholas Laughton

    April 20, 2021 at 5:57 am

    Hi I have found this really helpful. Thank you!
    Is there an easy change to search if the filed is a number or Text?

    I tried changing DIM RecordRow as LONG to Dim RecordRow as String

    and then CLng to Cstr but cannot get it to work, any tips would be much appreciated.

    Reply
    • Catalin Bombea

      April 20, 2021 at 12:49 pm

      Hi Nicholas,
      Try:
      If IsNumeric(RecordRow) Then…

      Reply
  5. P.C.BALAJI

    March 25, 2021 at 7:22 pm

    i have excel sheet of daily receipts for real estate plot sales i want to take outstanding report from the total plot value and receipts against plot is mentioned in excel i want to write macro to get the outstanding report for all projects data will be like this

    date. plot no. customer name total cost of plot receipt amount remarks project name

    daily receipts will be

    Reply
    • Catalin Bombea

      March 28, 2021 at 2:49 am

      Hi,Please upload a sample file with your data with a clear description of what your outcome should look ike, we’ll help you.
      Her is a link to our forum, you can create there a new topic for upload.

      Reply
  6. Doug Richards

    January 22, 2021 at 5:19 am

    Thank you for the video and code for search form, I used the VBA code and got it to work, but have an issue of getting the multi column results to line up under the search boxes, is there a way to adjust the distance between the results?

    Reply
    • Catalin Bombea

      January 22, 2021 at 12:32 pm

      Hi Doug,
      You have to manually arrange the objects in the form (drag and drop), set font size, heights, widths.

      Reply
  7. Slasharino

    September 4, 2020 at 4:24 am

    Hi,
    How would you handle if the search finds multiple entries. For instance, I am using the last name as the search criteria. I want it to prompt me if there are multiple last names found.
    Thank you.

    Reply
    • Philip Treacy

      September 5, 2020 at 3:57 pm

      Hi,

      You can use this code

      https://www.myonlinetraininghub.com/display-all-matches-from-search-in-userform-listbox

      Regards

      Phil

      Reply
  8. Peter Hill

    April 28, 2020 at 9:40 pm

    Thanks Philip. Dealing with dates is an issue that vexes me. I added another column to the worksheet formatted as short date (English (Australia)). I then added a sixth textbox to the form and an additional line of code to return the date to the textbox. Problem is that a date of birth of 1st Feb 1980 expressed on the worksheet as 1/2/1980 is returned to the textbox as 2/1/1980. Is there an easy solution to this? If we extend the use of the form to write data back to the worksheet after making changes on the form, will the date will be written back incorrectly?

    Reply
    • Catalin Bombea

      April 29, 2020 at 5:17 am

      If you want to keep the settings to en-AU, you have to manipulate the string.
      Dim Dt as long, DateParts as Variant
      DateParts=Split(TextBox1.Value,”/”) ‘DateParts will contain 3 items, if the text is like “21/10/2019”
      ‘DateSerial has 3 arguments, in this order: year-month-day
      Dt=CLng(DateSerial(DateParts(2),DateParts(1),DateParts(0)))
      ‘put the Dt value in the cell:
      Cells(1,1).Value=Dt ‘the cell format will properly display this value

      Reply
  9. Christopher Pieczora

    February 4, 2020 at 2:24 am

    Hi there, thanks for this post on designing a search form. I have created a search box using the code that you provided. When running it a receive the message “Not Found”. Wondering, your code looks for a “Number”, my table does not have a”Number”. I have 8 fields in my table. Would the “Number” be the problem?
    Any help would be greatly appreciated

    Reply
    • Catalin Bombea

      February 4, 2020 at 4:43 pm

      Hi Christopher,
      Can you please upload your sample file on our forum to see what happens in that file? (create a new topic after sign up)

      Reply
  10. lea cohen

    January 31, 2020 at 12:12 pm

    Hi,
    Thank you! Very useful!
    Question: I do not know VBA and would like to transfer the form to other files.
    How to do it?

    Reply
    • Catalin Bombea

      February 3, 2020 at 2:40 am

      Hi Lea,
      From VB Editor (press Alt+F11 to display it), right click the form in project explorer and choose Export (select the export folder you want).
      Then, in your destination file, use the import option to import the form you have just exported from the previous file.

      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.