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.

You can download the file.