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