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