Fix an Excel VBA Search Button That Returns No Results
Question details
The user needs an Excel VBA search form to find an employee ID and display the corresponding employee details.

- Product
- Microsoft Excel VBA UserForm
- Device & OS
- not provided
- Scenario
- An employee enters an ID into a search form and clicks a button to retrieve details from a worksheet.
- Observed behavior
- The search does not populate the name and employee detail fields.
Check the worksheet name, the ID and detail columns, and the names of the UserForm controls in the VBA editor. The example below assumes IDs are in column A, details are in columns B through D, and row 1 contains headers.
Qualify the worksheet references in the search button code
Use this approach when the search form reads employee IDs from a worksheet and should populate text boxes from the matching row.
The example uses ThisWorkbook.Worksheets("Sheet1") and qualifies both Cells and Rows with the same worksheet. It assumes the form and worksheet are in the workbook containing the VBA code.
Replace Sheet1, cmdSearch, txtID, txtName, txtDetail1, and txtDetail2 with your actual worksheet and control names. Store IDs consistently as text if leading zeros are significant.
In Excel, select Developer > Visual Basic. In Project Explorer, open the UserForm and double-click its search button to open its Click event.
Replace the button's Click procedure with the following example, adjusting the sheet and control names: Private Sub cmdSearch_Click() Dim ws As Worksheet Dim lastRow As Long Dim r As Long Dim searchID As String Set ws = ThisWorkbook.Worksheets("Sheet1") searchID = Trim$(Me.txtID.Value) Me.txtName.Value = "" Me.txtDetail1.Value = "" Me.txtDetail2.Value = "" If searchID = "" Then MsgBox "Enter an employee ID." Exit Sub End If lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row For r = 2 To lastRow If Trim$(CStr(ws.Cells(r, 1).Value)) = searchID Then Me.txtName.Value = ws.Cells(r, 2).Value Me.txtDetail1.Value = ws.Cells(r, 3).Value Me.txtDetail2.Value = ws.Cells(r, 4).Value Exit Sub End If Next r MsgBox "Employee ID not found." End Sub
Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), reopen it if necessary, and enable macros only if you trust the file. Open the UserForm, enter an ID from column A, and click the search button.

Work with employee spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday spreadsheet work. It does not repair an Excel VBA project, and macro support may vary by version and configuration.

Frequently Asked Questions
Why should I use Worksheets instead of Worksheet in the VBA code?
Worksheets is the collection used to identify a sheet by name, such as ThisWorkbook.Worksheets("Sheet1"). A Worksheet variable represents one sheet, but Worksheet("Sheet1") is not the correct collection reference.
Why does the code use ws.Rows.Count instead of Rows.Count?
Qualifying Rows.Count and Cells with ws ensures the last-row calculation uses the intended worksheet rather than whichever sheet happens to be active.
Why is an ID with leading zeros not found?
Excel may store the ID as a number even if formatting displays leading zeros. Store IDs as text in column A and enter the same text in the search box, or adapt the comparison to your ID format.
What if clicking the search button does nothing?
In Developer > Visual Basic, double-click the button in the UserForm designer and check that the search code is in its Click event. Also confirm that the workbook is saved as .xlsm and that macros are enabled for the trusted file.




