Home > Macros >UserForms >Search Box

Excel UserForm Search Add Edit

Use 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 Edit

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.

Video: Transaction Search UserForm Demo

To see how the Transaction Search Form works, watch this short demonstration video

Transaction Search Features

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.

• Search: Enter criteria in one or more of the search boxes. Then, click the Search button, to see a list of matching records.
• Edit: Click one of the records in the list, and its details appear in the text boxes. You can edit any of the fields, then click the Update button, to update the record in the Excel file.
• Add/Delete: You can also add new records, or delete existing records.

Video: Transaction Search Workbook Setup

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

Transaction Search Workbook Setup

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.

Menu Sheet

On the Menu sheet, there are 3 button that run macros:

• Add / Edit Transactions runs the ShowForm_Trans macro
• View Summary runs the ViewSummary macro
• View Data Sheet runs the ViewData macro

TransData

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

• Column A contains a formula: =ROW()
  • The row number is used in the update macro, to ensure that the correct record is updated
• In column B, the TransID is a unique number (manually entered), assigned to each record.
• Cell A1 is named RowHeadTD

AcctSummary

On the AcctSummary sheet:

• A pivot table summarizes all the transactions on the TransData sheet
• There is event code on the worksheet's code module, that updates the pivot table when the sheet is activated

Admin_Search

There are 4 sections on the Admin_Search sheet, and those sections are used by the UserForm macros.

1) Search Criteria

In cells C1:E2, there is a criteria area that is used in an Advanced Filter, for the Search boxes.

• C1:E2 is a named range – CritSearch
• When you click the Search button in the UserForm, code enters the search values into the blue cells.
• The headings in C1:E1 are an exact match for headings in the table with the transaction data.

2) Transaction ID

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

• Cell H2 is named TransIDSel
  • UserForm macro enters the selected transaction ID number there
• Cell I2 is named TransIDSelRow
  • its formula checks to see if the entered Transaction ID is already in the TransData sheet.
  • =IFERROR(MATCH(TransIDSel, tblTransData[TransID],0) +K2,0)
  • If the ID is found, its row number on the TransData sheet is returned

3) Table Heading Row

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

• The following formula, in cell K2, returns the row number for the named range, RowHeadTD.
  • =ROW(RowHeadTD)
• The RowHeadTD range is in the Transaction Data table
  • It is the heading cell for the Row formula column
• In another macro, number in cell K2 is added to number in cell I2

4) Next ID Number

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

• =MAX(tblTransData[TransID])+1
• MAX function finds highest ID number currently in the transaction data table
• Then, 1 is added to that number

SearchResults

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.

• DO NOT enter or edit the data on this sheet

Admin_Accts

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

Account Types

In column C, there is a small table, with only one column, and two entries.

• Table is named tblAcctTypes
• Two account types are listed - Income and Expense
• This list could be used for data validation drop down lists, in the worksheet lists

Accounts

In columns E:G, there is a 3-column table, with details on the accounts used in the sample workbook.

• AcctID - Unique account number
• Account - Short name that describes the account
• AcctType - One of the items from the AcctType table - Expense or Income

More accounts could be added to this table - as many as are needed for the transaction data.

Admin_Lists

On the Admin_Lists sheet, there is a list of companies in the transaction data

• This is a unique lists of companies, from the records in the transaction data
• DO NOT add companies to this list (This warning is also shown on the worksheet)
• List is updated automatically when the UserForm opens
• List is used for the Company search box on the UserForm

UserForm Search Boxes

To see how the search boxes work, in the Transaction UserForm, watch this short video. There are written notes below the video.

Use the Search Boxes

There are three search boxes on the Transaction Data Entry form:

• Trans ID
• Account
• Company

Note: Details on using each search box are in the sections below the next screen shot.

Search - General Steps

You can enter criteria in one or more of the 3 search boxes, then:

• Click the Search button to see the matching records
• Click the Clear button to start a new search.

Search - See All Records

To see all the records, follow these steps:

• Click the Clear button, to clear the search boxes and results list
• Click the Search button, to show all transaction records in the results list

Trans ID Search Box

You can type a number in the Trans ID search box, to find the matching record.

Or, use operators with a number, such as:

• >10 Greater than 10
• <=15 Less than or equal to 15
• <>3 Not equal to 3

Search Boxes - Drop Down Lists

The 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 Boxes

In the Account and Company search boxes, you can use wildcards with the text.

• Asterisk (*) wildcard character represents any number of characters in that position, including no characters.
• Question mark (?) wildcard character represents one characters in that position
• Tilde (~) wildcard character lets you search for characters that are normally used as wildcards.

For example:

• *inc* -- Contains "inc" anywhere in the text string
• *Ltd -- Ends with Ltd
• Te?t -- Contains "Te" then any 1 character then "t", e.g. Text or tent
• ~* -- Contains an asterisk

Exact Match in Text Search Boxes

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:

• Test, Testco, Testing Inc., etc.

To find only the records that contain "Test", and no other text, enter this formula in the search box:

• ="=Test"

NOTE: The case of the search item is not matched (upper case or lower case). Both Test and test are returned.

Add New Transaction Record

There are two ways to create a new transaction record:

1) Start from Blank

• Click the Clear button, to clear the UserForm
• Enter a new, unused ID number - you can use the Add Next ID button for this
• In the data entry boxes, enter the data for the new record
• Click the Add button

2) Start from Existing Record

• Use the Search boxes to find an existing record
• Delete the Transaction ID, and enter a new, unused ID number
• Modify any other data entry fields, if needed
• Click the Add button

UserForm VBA Code Setup

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

Transaction Search UserForm Code Setup

In this section, there are notes on how the Transaction UserForm works, behind the scenes:

• what the VBA code does, when you use one of the controls (buttons, list, search boxes)
• special settings for the control properties, such as width, or column counts

To see the actual VBA code, go to the UserForm VBA Code section

UserForm Initialize

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.

Search Boxes

There is no code on the Search boxes.

The Account combo box has the following property settings:

• 3 columns -- widths are set to show the contents of each column.
• List Width (the drop down list) is set to 130 pts, which is the total of the 3 column widths.
• Combobox Width is narrower – about 90 pts
  • drop down list extends beyond the combo box, when it is clicked.

Search Button

When you click the Search button:

• First, search criteria are copied to the Criteria Range on the Admin_Search sheet.
• Next, transaction records that match the criteria are copied to the SearchResults sheet, using an Advanced Filter.
• Next, the range with the extracted records is named rngRes
• Row Source for the UserForm listbox is updated, to use that named range.

Enter Next ID 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

Results ListBox

The UserForm listbox has 9 columns,

• width of the first column is set to 0 pts, to hide the Row number

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 Button

When you click the Update button:

• a message asks you to confirm that you want to update the selected record.
• If you click Yes, the record is updated on:
  • TransData sheet
  • SearchResults sheet
  • UserForm results listbox.

Add Button

When you click the Add button, these steps happen

• First, macro code checks to make sure that the Transaction ID has not been used before.
• Next, a message asks you 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.

Close Button

There is code in the Close button's Click event, to unload the UserForm.

Account Name Combo Box

There 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 Code

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.

View VBA Code in Sample File

To see the VBA code for the Transaction UserForm in the sample file, follow these steps:

• Open the Transaction UserForm sample file
• Press the Alt + F11 keys, to open the Visual Basic Explorer.
• At the left, in the Project Explorer, find the excelformtransactionsearch.xlsm file
• Click the plus sign to the left of its name, to see all the objects
• In its Forms section, right-click on frmTrans, and click View Code

UserForm Variables

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.

List of Module Variables

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

Option Explicit

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.

UserForm Opens

When the UserForm opens, the UserForm_Initialize code runs automatically

It calls two other procedures (macros):

• ClearDEControls
• ClearSearchControls

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

ClearDEControls

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

ClearSearchControls

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

SearchListUpdate

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

Search Button Click

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

ResultsListUpdate

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

Results Listbox Click

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

Enter Next ID Button

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

Update Button Click

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

Add Button Click

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

Close Button Click

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