Home > Macros >UserForms >Search Box Excel UserForm Search Add EditUse this Excel UserForm to work with transaction records in a worksheet table. Add new records, or use Search criteria to find existing records, and view, edit or delete them. NOTE: This page has a completed UserForm that you can download, and has detailed notes on how the UserForm works. This page does not show how to build this UserForm from scratch. |
UserForm Search Add EditIn the UserForm that you can download from this page, you can search for records that match criteria. Next, select a record in the results list, then update or delete that record, or add a new record. NOTE: For instructions for building a simple Excel UserForm from scratch, go to this page: Video: Transaction Search UserForm DemoTo see how the Transaction Search Form works, watch this short demonstration video |
Transaction Search FeaturesIn this UserForm, you can add new transaction records to the dataset, or search for existing records, and then view, edit or delete them. IMPORTANT: Each record should have a unique transaction number.
|
Video: Transaction Search Workbook SetupTo see how the Transaction Search workbook is set up, you can watch this short demonstration video. There are written notes about the workbook setup, below the video. NOTE: The lists are all on one sheet in the video. In the sample file, the lists have been split onto two sheets |
Transaction Search Workbook SetupThere are 7 sheets in the workbook – Menu, TransData, AcctSummary, Admin_Search, Admin_Accts and Admin_Lists. You can hide all the sheets, except the Menu and AcctSummary, to prevent accidental tampering with the data and lists. NOTE: If you protect the worksheets, you might need to add code that unprotects the sheets, to some of the macros, then re-protect the sheets at the end of the macro. Menu SheetOn the Menu sheet, there are 3 button that run macros:
TransDataOn the TransData sheet, there is a formatted Excel table, named tblTransData. The table has sample records that you can use for testing. Later, you can delete the sample data, and enter your own transaction data
AcctSummaryOn the AcctSummary sheet:
|
Admin_SearchThere are 4 sections on the Admin_Search sheet, and those sections are used by the UserForm macros. 1) Search CriteriaIn cells C1:E2, there is a criteria area that is used in an Advanced Filter, for the Search boxes.
2) Transaction IDIn the second section of the Admin_Search sheet, a formula checks if a Transaction ID has been used previously.
3) Table Heading RowIn the third section, a formula finds the row number for the transaction data table headings.
4) Next ID NumberIn the fourth section, a formula finds the next available transaction ID number. The following formula, in cell N2, returns the next available transaction ID number
SearchResultsThis sheet has transaction records that are exported by a UserForm macro, when you click the Search button. Or, the sheet might be empty, if search results were cleared.
|
Admin_AcctsOn the Admin_Accts sheet, there are two formatted Excel tables - Account Types, and Accounts Account TypesIn column C, there is a small table, with only one column, and two entries.
AccountsIn columns E:G, there is a 3-column table, with details on the accounts used in the sample workbook.
More accounts could be added to this table - as many as are needed for the transaction data. Admin_ListsOn the Admin_Lists sheet, there is a list of companies in the transaction data
|
UserForm Search BoxesTo see how the search boxes work, in the Transaction UserForm, watch this short video. There are written notes below the video. Use the Search BoxesThere are three search boxes on the Transaction Data Entry form:
Note: Details on using each search box are in the sections below the next screen shot. Search - General StepsYou can enter criteria in one or more of the 3 search boxes, then:
Search - See All RecordsTo see all the records, follow these steps:
Trans ID Search BoxYou can type a number in the Trans ID search box, to find the matching record. Or, use operators with a number, such as:
Search Boxes - Drop Down ListsThe Account and Company search boxes have drop down lists. You can select an item from the list, or type in the box. Wildcards in Text Search BoxesIn the Account and Company search boxes, you can use wildcards with the text.
For example:
Exact Match in Text Search BoxesIn the Account and Company search boxes, if you type a text string, it will find all the records that begin with that text. For example, enter "Test" and the results could include company names such as:
To find only the records that contain "Test", and no other text, enter this formula in the search box:
NOTE: The case of the search item is not matched (upper case or lower case). Both Test and test are returned. |
Add New Transaction RecordThere are two ways to create a new transaction record: 1) Start from Blank
2) Start from Existing Record
|
UserForm VBA Code SetupTo see how the Transaction Search UserForm macro VBA code is set up, you can watch this short video. Notes: There are notes on how the code works, below the video. VBA Code: The VBA code from the Transaction UserForm is in the section below the notes Transaction Search UserForm Code SetupIn this section, there are notes on how the Transaction UserForm works, behind the scenes:
To see the actual VBA code, go to the UserForm VBA Code section UserForm InitializeWhen the form opens the Initialize code updates the lists of Accounts and Companies. These lists are created with the Advanced Filter feature, and stored on the Admin_Lists sheet. Search BoxesThere is no code on the Search boxes. The Account combo box has the following property settings:
Search ButtonWhen you click the Search button:
Enter Next ID ButtonWhen you click the Enter Next ID button, a macro enters the next available transaction ID in the Transaction ID box. A formula on the Admin_Search sheet calculates that number, based on the existing entries in the stored list of transactions Results ListBoxThe UserForm listbox has 9 columns,
When you click an item in the List Box, code runs, and copies the data from that row to the text boxes, below the list box. Update ButtonWhen you click the Update button:
Add ButtonWhen you click the Add button, these steps happen
Close ButtonThere is code in the Close button's Click event, to unload the UserForm. Account Name Combo BoxThere is code in the Account Name Combo Box After Update event, which enters the Account Number and Account Type for the selected Account. |
Transaction Search UserForm VBA CodeThis section has the VBA code from the Transaction UserForm workbook. You can also see the code in the sample file (download in the next section), and test it there. View VBA Code in Sample FileTo see the VBA code for the Transaction UserForm in the sample file, follow these steps:
UserForm VariablesAt the top of the UserForm code module, there is a list of variables, each starting with Dim. Because they are in the Declarations area of the module, these variables can be used by any of the procedures within the module. Tip: Read more about Declaring Variables in VBA code, on the Microsoft website. List of Module VariablesHere is the list of variables at the top of the UserForm module Dim rngTrans As Range Dim rngRes As Range Dim lRowList As Long Dim bEventsOff As Boolean Dim wsTD As Worksheet Dim wsSR As Worksheet Dim wsAS As Worksheet Option ExplicitAt the very top of the module, before the list of variables, is this line of code: Option Explicit That Option Explicit statement forces you to declare all variables, before you use them. If your code has an undeclared variable name, you'll see an error message, if you try to run the code, or decompile it. Tip: It's good practice to include the Option Explicit statement, to help prevent mistyped variable names, or other types of errors. UserForm OpensWhen the UserForm opens, the UserForm_Initialize code runs automatically It calls two other procedures (macros):
These two procedures prepare the UserForm for a new search. Note: The code for each of these procedures is shown in the sections below. Private Sub UserForm_Initialize() ClearDEControls ClearSearchControls End Sub |
ClearDEControlsThe ClearDEControls procedure clears all the data entry controls on the UserForm Sub ClearDEControls() 'clear the data boxes Me.txtTransID.Value = "" Me.txtDate.Value = "" Me.cboCompany.Value = "" Me.cboAcct.Value = "" Me.txtDesc.Value = "" Me.txtAmount.Value = "" End Sub ClearSearchControlsThe ClearSearchControls procedure clears all the search controls on the UserForm, and runs two additional macros – ClearSearchData and SearchListUpdate -- that update the lists. It also clears the ListBox. CODE SearchListUpdateThe SearchListUpdate macro runs the CompanyListCreate macro, to get an updated company list. It also sets the RowSource property for each search box. Sub ClearSearchControls() 'clear the search boxes Me.txtSearch01.Value = "" Me.txtSearch02.Value = "" Me.txtSearch03.Value = "" 'clear data on search sheets ClearSearchData 'get latest data for search box drop down lists Me.txtSearch02.RowSource = "" Me.txtSearch03.RowSource = "" SearchListsUpdate 'clear the results list Me.lstResults.ListIndex = -1 Me.lstResults.RowSource = "" End Sub |
Search Button ClickWhen you click the Search button, the ResultsListUpdate macro runs, to show the matching records in the ListBox. Private Sub cmdSearch_Click() ResultsListUpdate exitHandler: Application.ScreenUpdating = True Exit Sub errHandler: Resume exitHandler End Sub ResultsListUpdateThe ResultsListUpdate macro clears the controls, and copies the search criteria to the Criteria Range on the Admin_Search sheet. Then, it runs a filter, to extract the records from the data sheet, that match the selected criteria. If matching records are found, it creates a named range, rngRes, on the Results sheet, based on those records. The Row Source for the ListBox is set to that named range. Then, TransData records that match the criteria are copied to the SearchResults sheet, using an Advanced Filter. Those records are shown in the ListBox. The range with the extracted records is named rngRes, and the Row Source for the listbox is updated, to use that named range. Sub ResultsListUpdate() Dim rngResStart As Range Dim lRowSR As Long Set wsTD = wksData Set wsAS = wksAS Set rngTrans = wsTD.Range("RowHeadTD") _ .CurrentRegion Set rngTrans = rngTrans.Offset(1, 0) _ .Resize(rngTrans _ .Rows.Count - 1, rngTrans.Columns.Count) Application.ScreenUpdating = False Set wsSR = wksSR Set rngResStart = wsSR.Range("A1") Me.lstResults.ListIndex = -1 ClearDEControls ClearSearchData With wsAS.Range("CritSearch") _ .Range(wsAS.Cells(2, 1), wsAS.Cells(2, 3)) .ClearContents .Value = Array(Me.txtSearch01.Value, _ Me.txtSearch02.Value, _ Me.txtSearch03.Value) End With 'filter matching records to results sheet wsTD.Columns("A:I").AdvancedFilter _ Action:=xlFilterCopy, _ CriteriaRange:=wsAS.Range("critSearch"), _ CopyToRange:=rngResStart, _ Unique:=False lRowSR = 1 'find last row in results list lRowSR = wsSR.Cells.Find(What:="*", _ After:=rngResStart, SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, _ LookIn:=xlValues).Row If lRowSR = 1 Then lstResults.RowSource = "" Else Set rngRes = rngResStart.CurrentRegion Set rngRes = rngRes.Offset(1, 0) _ .Resize(lRowSR - 1, rngRes.Columns.Count) ActiveWorkbook.Names.Add _ Name:="rngRes", RefersTo:=rngRes lstResults.RowSource = "rngRes" End If exitHandler: Application.ScreenUpdating = True Exit Sub errHandler: Resume exitHandler End Sub |
Results Listbox ClickWhen you click an item in the List Box, code runs, and copies the data from that row to the text boxes, below the list box. Private Sub lstResults_Click() If bEventsOff Then Exit Sub With lstResults lRowList = .ListIndex Me.txtTransID.Value = .List(lRowList, 1) Me.txtDate.Value = _ Format(.List(lRowList, 2), "Short Date") Me.cboCompany.Value = .List(lRowList, 3) 'hidden column 0 has acct ID Me.cboAcct.Value = _ .List(lRowList, 5) Me.txtDesc.Value = .List(lRowList, 7) Me.txtAmount.Value = .List(lRowList, 8) End With End Sub Enter Next ID ButtonWhen clicked, enters the next available transaction ID in the Transaction ID box. A formula on the Admin_Search sheet calculates that number, based on entries on the data sheet. Private Sub cmdNexID_Click() Dim wsAS As Worksheet Set wsAS = wksAS On Error Resume Next wsAS.Range("NextTransID").Calculate Me.txtTransID.Value _ = wsAS.Range("NextTransID").Value End Sub |
Update Button ClickWhen you click the Update button, you will be asked to confirm that you want to delete (all controls blank) or update the selected record. If you click Yes, the record is updated on the TransData sheet, on the SearchResults sheet, and in the UserForm results list. The Row ID from the selected item in the ListBox is used to match the record on the different sheets. At the end of the procedure, the lists are updated, to show any new information. Private Sub cmdUpdate_Click() Dim lRowTD As Long On Error Resume Next lRowTD = lstResults.List(lstResults _ .ListIndex, 0) On Error GoTo 0 If lRowTD = 0 Then Exit Sub bEventsOff = True If txtTransID = "" And txtDate = "" _ And cboCompany = "" And _ cboAcct = "" And txtDesc = "" _ And txtAmount = "" Then If MsgBox("Delete This Record?", _ vbExclamation + vbYesNo, _ "Delete?") = vbNo Then bEventsOff = False Exit Sub Else wsTD.Rows(lRowTD).EntireRow.Delete lRowList = lstResults.ListIndex + 1 rngRes.Rows(lRowList).EntireRow.Delete End If Else If MsgBox("Update this record?", _ vbExclamation + vbYesNo, _ "Update?") = vbNo Then bEventsOff = False Exit Sub Else With wsTD .Cells(lRowTD, 2).Value = CLng(txtTransID) .Cells(lRowTD, 3).Value = CDate(txtDate) .Cells(lRowTD, 4).Value = cboCompany 'AcctType .Cells(lRowTD, 5).Value = _ cboAcct.List(cboAcct.ListIndex, 2) 'Acct ID .Cells(lRowTD, 6).Value = cboAcct.Value 'Acct Name .Cells(lRowTD, 7).Value = cboAcct.Text .Cells(lRowTD, 8).Value = txtDesc .Cells(lRowTD, 9).Value = CDbl(txtAmount) End With With rngRes lRowList = lstResults.ListIndex + 1 .Cells(lRowList, 2) = CLng(txtTransID) .Cells(lRowList, 3) = _ Format(CDate(txtDate), "Short Date") .Cells(lRowList, 4) = cboCompany 'AcctType .Cells(lRowList, 5).Value = _ cboAcct.List(cboAcct.ListIndex, 2) .Cells(lRowList, 6).Value _ = cboAcct.Value 'Acct ID .Cells(lRowList, 7).Value _ = cboAcct.Text 'Acct Name .Cells(lRowList, 8) = txtDesc .Cells(lRowList, 9) = CDbl(txtAmount) End With SearchListsUpdate End If End If bEventsOff = False End Sub |
Add Button ClickWhen you click the Add button, the code checks to make sure that the Transaction ID has not been used before. Then, you will be asked to confirm that you want to add the record. If you click Yes, the record is added on the TransData sheet, on the SearchResults sheet, and in the UserForm results list. Private Sub cmdAdd_Click() Set wsTD = wksData Set wsAS = wksAS Dim lRowTD As Long On Error Resume Next lRowTD = wsTD.Cells.Find(What:="*", _ After:=wsTD.Range("A1"), _ SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, _ LookIn:=xlValues).Row + 1 On Error GoTo 0 If lRowTD = 1 Then Exit Sub bEventsOff = True wsAS.Range("TransIDSel").Value _ = Me.txtTransID.Value If txtTransID = "" Then MsgBox "A transaction ID is required." bEventsOff = False Exit Sub End If If wsAS.Range("TransIDSelRow") _ .Value <> 0 Then MsgBox "That transaction ID has been used." bEventsOff = False Exit Sub End If If Me.cboAcct.ListIndex < 0 Then MsgBox "Please select a valid Account Name." bEventsOff = False Exit Sub End If If MsgBox("Add this record?", _ vbExclamation + vbYesNo, "Add Record?") _ = vbNo Then bEventsOff = False Exit Sub Else With wsTD '.Cells(lRowTD, 1).Formula = "=ROW()" .Cells(lRowTD, 2).Value = CLng(txtTransID) .Cells(lRowTD, 3).Value = CDate(txtDate) .Cells(lRowTD, 4).Value = cboCompany 'AcctType .Cells(lRowTD, 5).Value = _ cboAcct.List(cboAcct.ListIndex, 2) 'Acct ID .Cells(lRowTD, 6).Value = cboAcct.Value Acct Name .Cells(lRowTD, 7).Value = cboAcct.Text ' .Cells(lRowTD, 8).Value = txtDesc .Cells(lRowTD, 9).Value = CDbl(txtAmount) End With ClearSearchControls SearchListsUpdate End If bEventsOff = False End Sub Close Button ClickThere is code in the Close button's Click event, to unload the UserForm. Private Sub cmdClose_Click() Unload Me End Sub |
![]() |
Download the completed Transaction Search Edit UserForm sample file. 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 |
Last updated: May 14, 2023 11:57 AM