VBA & Macro Problems

Search Worksheets with an Excel VBA ComboBox and ListBox

Algirdas JasaitisAlgirdas Jasaitis Nov 20, 2026 869 views

Question details

Create an Excel UserForm that searches employee records on another worksheet and exports a matching record.

Search Worksheets with an Excel VBA ComboBox and ListBox
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.
Before you start

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.

Solution 1Recommended

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.

1
Name the source range

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.

2
Create the UserForm

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.

3
Add the form code

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

4
Run and test the form

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.

Search a named range and export a selected ListBox row
Check the destination layout: The export code finds the next row using column A. Keep column A populated for existing records on the Export sheet so new results are appended as expected.
Free Microsoft Office alternative

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.

Review and filter employee records in WPS SpreadsheetsOpen and edit common Excel formats, including XLSXUse a familiar spreadsheet interface for an easier transitionWork with a free, lightweight office suite
microsoft office alternative - wps office

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.