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
The form obviously needs fields for each column so the basic form design looks like this
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.
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.
All the code in this post can be downloaded in this workbook.
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.
I now know where the record is so I can set a range to the first cell of that record
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.
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.
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
Please post your question on our Excel forum where you can also upload a sample file and we can help you further.
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
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.
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.
Sorry I don’t clearly understand the problem. Can you please start a topic on our forum and attach your file with your explanation.
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.
If IsNumeric(RecordRow) Then…
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
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.
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?
You have to manually arrange the objects in the form (drag and drop), set font size, heights, widths.
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.
You can use this code
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?
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
‘put the Dt value in the cell:
Cells(1,1).Value=Dt ‘the cell format will properly display this value
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
Can you please upload your sample file on our forum to see what happens in that file? (create a new topic after sign up)
Thank you! Very useful!
Question: I do not know VBA and would like to transfer the form to other files.
How to do it?
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.