In case if you have a large list of employee data in a sheet and you want to select one employee details in main sheet based on the employee number. You can put a vlookup table and the details can be retrieved based on id in that table as below.

But in case if you cant remember all employee id’s, you can create a small search box in excel VBA to search based on name and the table can be updated based on the selection from search results in listbox.

Display search result in listbox

To create this search box, you can create a userform with following objects.

• Textbox to enter search name with name Textbox1
• Search button with name CommandButton1 
• List box to give the search output with name Listbox1 
• OK button to exit the userform with name CommandButton2

The search box will look like as below:

Then put below code in Userform1 to search and display the result based on name of the employee.

Private Sub CommandButton1_Click()
' to search and display in listbox when search button is pressed.

Dim RowNum As Long

RowNum = 1
Do Until Sheets("Data").Cells(RowNum, 1).Value = ""

If InStr(1, Sheets("Data").Cells(RowNum, 2).Value, TextBox1.Value, vbTextCompare) > 0 Then
On erro GoTo next1
ListBox1.AddItem Sheets("Data").Cells(RowNum, 1).Value
ListBox1.List(ListBox1.ListCount - 1, 1) = Sheets("Data").Cells(RowNum, 2).Value
End If
next1:
RowNum = RowNum + 1
Loop
End Sub


Private Sub CommandButton2_Click()
' to exit from searchbox
Unload Me

End Sub

Private Sub ListBox1_Click()
' enter the id number in sheet when selected in listbox
Dim rowcount As Variant
rowcount = ListBox1.Column(0, ListBox1.ListIndex)

Sheets("Results").Range("B3").Value = rowcount

End Sub

To initiate the search box from Sheet you can insert a Shape and link to macro to put below simple code to call userform1.

Sub Searchbox()

UserForm1.Show

End Sub

When you search for the name in serchbox and click on Search, it will show the list of matching records in listbox as below:

You can select the employee number from list to show in the table.

Same logic or procedure can be applied for inventory search, location search and many more from the data dump.

Download Display Search results in listbox excel file