My Online Training Hub

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

You are here: Home / Excel VBA / Searching for Data With a User Form
search for data with userform
by

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.

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.

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

    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

  2. Carlo James

    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

      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

    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

      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

    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

  5. P.C.BALAJI

    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

      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

    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

  7. Slasharino

    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

  8. Peter Hill

    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

      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

    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

  10. lea cohen

    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

      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

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

Current ye@r *