When you have large amount of data in multiple sheets and you want to get the search results dynamically as you type , you can add a user form in excel vba and create a search box as below.
Display search result in listbox from all sheets as you type
To create this search box, you can create a userform with following objects.
- Insert a userform in vba window
- Textbox to enter search name with name Textbox1
- Add a label to textbox
- List box to give the search output with name Listbox1 with 3 columns
Add below code to userform to get the search results in listbox as you type in the textbox. This will give the results from all the sheets available in the workbook. This will also show the sheet name and the cell reference in which the search result is found in the workbook.
Private Sub TextBox1_Change()
Dim searchkey As Variant
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim cvalue As Variant
searchkey = TextBox1.Value
Set wb = ThisWorkbook
Me.ListBox1.Clear
For Each ws In wb.Worksheets
Set rng = ws.UsedRange
For Each cvalue In rng
If InStr(1, cvalue, searchkey, vbTextCompare) > 0 Then
On Error Resume Next
ListBox1.AddItem cvalue
ListBox1.List(ListBox1.ListCount - 1, 1) = ws.Name
ListBox1.List(ListBox1.ListCount - 1, 2) = cvalue.Address
End If
Next cvalue
Next ws
End Sub
To initiate this userform, you can add a shape in main sheet and link that to userform with below small code.
Sub Searchbox()
UserForm1.Show
End Sub
You will get the search results in the list box with sheet name and the cell reference as below when you search the word in the searchbox. This will update dynamically when you type in the textbox.
Download the example file here: excel-vba-search-listbox-as-you-type-23012022