Search Worksheets with an Excel VBA ComboBox and ListBox
Question details
Create an Excel UserForm that searches employee records on another worksheet and exports a matching record.

- Product
- Microsoft Excel 2010
- Device & OS
- not provided
- Scenario
- An employee enters a search term in a ComboBox, reviews matching rows in a ListBox, and exports a chosen result to another worksheet.
- Observed behavior
- The desired UserForm searches the first column of a named data range and displays matching employee records for selection.
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), and make sure the employee data is in one contiguous range with no header row, merged cells, or error values. The example supports up to 10 columns and uses the first column for searching.
Search a named range and export a selected ListBox row
Use a ComboBox Change event to update results as the user types, then export the selected record with a button.
This example searches for a case-insensitive partial match in the first column. It exports one selected row as values, without copying formatting or formulas.
On the employee data worksheet, select the data cells without the header row. Click the Name Box to the left of the formula bar, type Data, and press Enter. Create a separate worksheet named Export.
Open the VBA editor with Alt+F11 and choose Insert > UserForm. Add a ComboBox named ComboBox1, a ListBox named ListBox1, and a CommandButton named CommandButton1. Set the ListBox MultiSelect property to 0 - fmMultiSelectSingle.
Double-click the UserForm and paste this code into its code window: Private Sub ComboBox1_Change() Dim src As Range Dim r As Long, c As Long, itemIndex As Long Dim term As String Set src = ThisWorkbook.Names("Data").RefersToRange term = Trim$(Me.ComboBox1.Value) Me.ListBox1.Clear Me.ListBox1.ColumnCount = src.Columns.Count If Len(term) = 0 Then Exit Sub For r = 1 To src.Rows.Count If InStr(1, CStr(src.Cells(r, 1).Value), term, vbTextCompare) > 0 Then Me.ListBox1.AddItem CStr(src.Cells(r, 1).Value) itemIndex = Me.ListBox1.ListCount - 1 For c = 2 To src.Columns.Count Me.ListBox1.List(itemIndex, c - 1) = src.Cells(r, c).Value Next c End If Next r End Sub Private Sub CommandButton1_Click() Dim dest As Worksheet Dim nextRow As Long, c As Long If Me.ListBox1.ListIndex = -1 Then MsgBox "Select a record to export." Exit Sub End If Set dest = ThisWorkbook.Worksheets("Export") If Application.WorksheetFunction.CountA(dest.Cells) = 0 Then nextRow = 1 Else nextRow = dest.Cells(dest.Rows.Count, 1).End(xlUp).Row + 1 End If For c = 0 To Me.ListBox1.ColumnCount - 1 dest.Cells(nextRow, c + 1).Value = Me.ListBox1.List(Me.ListBox1.ListIndex, c) Next c End Sub
In the VBA editor, select the UserForm and press F5. Type part of a first-column value in ComboBox1, select a matching row in ListBox1, and click CommandButton1. Check the Export worksheet for the appended record.

Work with employee spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Microsoft Excel formats, making it easy to move everyday workbook tasks. VBA UserForm and macro support can vary by platform and installation, so verify your workbook before relying on this Excel-specific solution.

Frequently Asked Questions
Does the search require an exact match?
No. The InStr function in the ComboBox1_Change event finds partial matches in the first column and ignores case.
Why is my ListBox empty when I open the form?
The example clears the ListBox when the search box is blank. Type part of a value from the first column of the Data range to display matching rows.
How do I change which column is searched?
In the ComboBox1_Change code, change src.Cells(r, 1) in the InStr call to the desired column number within the Data range. The displayed row will still start with the range's first column.
Why does the export button ask me to select a record?
Click a matching row in ListBox1 before clicking CommandButton1. The example exports only the currently selected row.
Can I use this code with more than 10 data columns?
Not as written. The example uses AddItem and indexed ListBox values, so keep the Data range to 10 columns or fewer.




