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.