Display Search Results in a ListBox – Excel VBA

In this blog post, we will look at showing search results in a ListBox of a userform in Excel VBA.

We want a user to type into a text box, and the search results of that entry to appear in a ListBox. A search result can then be selected and added to a row on a spreadsheet.

This is the example.

We have the form below. The user enters the ID of a product, and VLOOKUP formulas return the related information from a product list into the other columns.

Sometimes though, the user does not know the product ID.

If so, they can click on the Advanced Search button. This opens the form below, where they can enter keywords to search for the product they need.

Then our VBA code will return the search results to the ListBox, and add the product they select to the form on the spreadsheet.

Watch the Video – Display Search Results in a ListBox

This video tutorial will take you through the entire process. You can also read on to see the VBA code and techniques used.

Inserting the ListBox Control

The first task is the insert the ListBox control and to set some key properties for it.

This tutorial is assuming that the other parts of the userform (text box and buttons) have already been created. This is done to keep this tutorial shorter and to the point.

Click on the ListBox control button and draw it onto the userform.

With the ListBox inserted, we shall change some important properties in the Properties Window (if you do not see this, click View > Properties Window).

The Name is changed to lstSearchResults so that it is easy to reference when writing the VBA code.

The ColumnCount setting is set to 3. This is because we want to show 3 columns of information in the ListBox (ID, Product Name and Quantity Per Unit)

The ColumnHeads property is set to True. This will show the first row above the row source selection as headers.

The ColumnWidths have been set to 20pt, 200pt and 80pt. This has been tested to work nicely with the data used.

Creating a Dynamic Named Range for the Search Results

We now want to create a dynamic named range for the search results. This will ultimately be used as the row source to populate the ListBox.

We are using a worksheet named Product Search. This worksheet is to be filled with all the products that meet the search criteria, and is then used to populate the ListBox.

The dynamic named range will give us a way of easily referencing this range.

Click the Formulas tab and the Define Name button.

Type a name for the range. We have used SearchResults.

Then this formula is used for the Refers to field.

=OFFSET('Product Search'!$A$2,0,0,COUNTA('Product Search'!$A:$A)-1,3)

Add the VBA Code for the Search Button

We now need some VBA code to return the search results to the ListBox dependent upon the keywords in the text box.

The code used can be seen below.

Private Sub cmdSearch_Click()
Dim RowNum As Long
Dim SearchRow As Long
RowNum = 2
SearchRow = 2
Worksheets("Stock Data").Activate
Do Until Cells(RowNum, 1).Value = ""
If InStr(1, Cells(RowNum, 2).Value, txtKeywords.Value, vbTextCompare) > 0 Then
Worksheets("Product Search").Cells(SearchRow, 1).Value = Cells(RowNum, 1).Value
Worksheets("Product Search").Cells(SearchRow, 2).Value = Cells(RowNum, 2).Value
Worksheets("Product Search").Cells(SearchRow, 3).Value = Cells(RowNum, 3).Value
SearchRow = SearchRow + 1
End If
RowNum = RowNum + 1
Loop
If SearchRow = 2 Then
MsgBox "No products were found that match your search criteria."
Exit Sub
End If
lstSearchResults.RowSource = "SearchResults"
End Sub

The Instr VBA function has been used to test if there is a match between the words written in the text box (txtKeywords) and each row of the products list.

Each time there is a match, the product is written to the Product Search sheet (where our dynamic named range will be pick it up).

The last statement then assigns this dynamic named range, which I called SearchResults, as the RowSource of the ListBox.

If you are new to Excel VBA and this seems too much. Enrol in our Excel VBA course for beginners to get up to speed fast.

VBA to Add the Product to the Form

Now we need some VBA code for the Add Product button.

Private Sub cmdAdd_Click()
Dim RowNum As Long
Dim ListBoxRow As Long
Worksheets("Form").Activate
RowNum = Application.CountA(Range("A:A")) + 2
ListBoxRow = lstSearchResults.ListIndex + 2
Cells(RowNum, 1).Value = Worksheets("Product Search").Cells(ListBoxRow, 1).Value
Unload Me
End Sub

The ListIndex property of the ListBox has been used here to detect which product the user selected.

This code has also been used on the Initialize event of the userform to clear the Product Search worksheet each time the form is opened. And to set the text box as the active field when the form is opened.

Private Sub UserForm_Initialize()
txtKeywords.SetFocus
Worksheets("Product Search").Range("A2:C100").ClearContents
End Sub

This example can be adapted as per your requirements, and serves as a realistic example of populating a ListBox with search results.

Download a copy of the completed display search results in a ListBox file.