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

In 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:
Create
an Excel UserForm with Combo Boxes.

To see how the Transaction Search Form works, watch this short demonstration video
In 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.
To 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
There 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.
On the Menu sheet, there are 3 button that run macros:

On 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

On the AcctSummary sheet:

There are 4 sections on the Admin_Search sheet, and those sections are used by the UserForm macros.
In cells C1:E2, there is a criteria area that is used in an Advanced Filter, for the Search boxes.

In the second section of the Admin_Search sheet, a formula checks if a Transaction ID has been used previously.

In the third section, a formula finds the row number for the transaction data table headings.

In 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
This 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.

On the Admin_Accts sheet, there are two formatted Excel tables - Account Types, and Accounts

In column C, there is a small table, with only one column, and two entries.
In 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.
On the Admin_Lists sheet, there is a list of companies in the transaction data

To see how the search boxes work, in the Transaction UserForm, watch this short video. There are written notes below the video.
There 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.
You can enter criteria in one or more of the 3 search boxes, then:
To see all the records, follow these steps:

You can type a number in the Trans ID search box, to find the matching record.
Or, use operators with a number, such as:
The Account and Company search boxes have drop down lists. You can select an item from the list, or type in the box.
In the Account and Company search boxes, you can use wildcards with the text.
For example:
In 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.
There are two ways to create a new transaction record:
To 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
In 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
When 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.
There is no code on the Search boxes.
The Account combo box has the following property settings:
When you click the Search button:
When 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
The 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.
When you click the Update button:
When you click the Add button, these steps happen
There is code in the Close button's Click event, to unload the UserForm.
There is code in the Account Name Combo Box After Update event, which enters the Account Number and Account Type for the selected Account.
This 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.
To see the VBA code for the Transaction UserForm in the sample file, follow these steps:

At 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.

Here 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
At 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.
When 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
The 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
The 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
The 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
When 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
The 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
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.
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
When 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
When 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
When 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
There 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 |
Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.
Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!
Create an Excel UserForm with Combo Boxes
UserForm TextBox Validation Code
Last updated: December 27, 2024 12:07 PM