Written by Debra Dalgleish from Contextures Blog
There’s been an update to my Excel Search Form Builder (SFB), so download the latest version if you’ve bought this product before. In version 4.08 you can create a form with either 2 or 4 search boxes. Previously, there were only 2.
Note: If you don’t have the Search Form Builder yet, it’s in the UserForms for Data Entry kit.
Video: Search Form Builder Demo
This short video shows the Search Form Builder’s 3 easy steps.
- It lists all fields from your Excel table
- You choose fields for the unique ID and Search Boxes
- It builds the Search Form
0. Preparation Steps
Before using the SFB, you need to do a few things:
- Save your workbook as macro enabled
- Format your list as a named Excel table
- Create named ranges as the source for any data validation drop down lists in the table
1. Build a Field List
After the preparation steps are done, select any cell in your named Excel table.
Then, choose step 2 in the SFB menu.
2. Customize the Field List
The SFB adds a new sheet to your workbook, with the field list, and instructions and settings on the right side of the sheet.
There’s a new setting in this version — the number of search boxes.
- Choose 2 or 4 from the drop down list.
Choose ID and Search Box Fields
Next, choose the fields that you want in the search form.
- You MUST select 1 field as the UniqueID field
- You MAY select 1-4 fields for the Search Boxes (based on your “Search Boxes” number)
3. Build the Search Form
When the field list is ready, go back to the SFB menu, and run step 3.
- The Search Form is built
- A new sheet is added to your workbook
- A button on that sheet open the Search Form
Use the Search Form
You can enter search criteria in one or more of the boxes at the top of the form, or leave them empty.
Then, click the Search button, to see the records that match your criteria. If no criteria were entered, the list will show all the records in your Excel table
Select a Record
In the list of search results, click on a record that you would like to work with.
The details for that record will appear in the text boxes and combo boxes on the form.
NOTE: The boxes on on a multi-page control, so click the page buttons to see all the data, if necessary.
Edit a Record
After you select a record, you can:
- change one or more of the fields in the selected record, then click Update .
- or click Add, to put the revised data in a new record
- or delete the selected record
- When you’re finished, click Close
Get the Search Form Builder Add-in
If you don’t have the Search Form Builder yet, it’s in the UserForms for Data Entry kit.
The kit also has step-by-step videos and written instructions, for building an Excel UserForm from scratch.
More Search Form Builder Help
See more details and tips on the Search Form Builder Help page, on my Contextures site.
The Search Form Builder is designed to add one search form per workbook. However, if you need another one, there are detailed steps for adding a second Search Form.
There are also a tips on tweaking the search form code, if you need them.
Excel Search Form Builder Update 2020-06
Excel Search Form Builder Update 2020-06 is a post from Contextures Blog and is not allowed to be copied to other sites