There’s an Excel UserForm that you can download from my Contextures site, and use it to search, view, and edit records in an Excel table. The two short videos below show how the UserForm works, and how you can put flexible criteria in the search boxes.
And remember – it’s almost Spreadsheet Day – we’ll celebrate on Monday, October 17th!
UserForm Search Add Edit
The UserForm is in an Excel workbook that has a named Excel table, with sample income and expense Transaction records, for a fictional small business.
After you open the workbook:
- Click a worksheet button to open the UserForm.
- Next, put criteria in one or more of the Search boxes – Transaction ID, Account, or Company
- When you’re ready, click the Search button, to see the matching records.
In the screen shot below, a name was selected in the Company search box, and three transactions from the selected company are shown in the Search Results list.
Work with Transaction Data
After you click Search, and the results list appears, you can work with any of the listed records.
- First, click on any record in the results list
- That record’s data appears in the data entry cells of the UserForm, below the results list
- You can view the data, OR take one of the following actions:
- update one or more of the data entry fields, then click the Update button.
- delete the selected record, if it’s not needed any longer
- change one or more fields, assign a new ID, and add to the existing data as a new record
Completed UserForm Excel File
The UserForm workbook is a completed Excel example, so you can use it as is, or make changes it.
In the workbook, the VBA project is unlocked, so you can:
- make changes to the UserForm design
- edit the VBA code
- or, just take a look at the code and design, to see how it works.
The page does NOT have instructions for building the UserForm, or changing the code.
- NOTE: If you’d like instructions for building a simple Excel UserForm from scratch, go to this page — Create an Excel UserForm with Combo Boxes. That page has written steps, and videos, to help you get started.
Video: Transaction Search UserForm Demo
To see how the Transaction Search Form works, watch this short video.
Note: There’s more detail on using the Search Boxes, in the next video.
UserForm Search Boxes
To see how the search boxes work, and the different ways to enter criteria, watch this short video.
Get the UserForm Workbook
To get the Excel sample file, with the completed Transaction Search UserForm, go to the UserForm Search Add Edit page on my Contextures site.
The Excel file is in xlsm format, and contains macros and an Excel UserForm. Be sure to enable macros when you open the workbook, if you want to test the UserForm.
Search Transaction Records with Excel UserForm
Original source: https://contexturesblog.com/archives/2022/10/06/search-transaction-records-with-excel-userform/?utm_source=rss&utm_medium=rss&utm_campaign=search-transaction-records-with-excel-userform