Searching A Value Across An Entire Workbook With Userform

Excel Find A Value In Workbook With Userform - Reporting The Results

A great userform example for searching data on the selected sheet or the entire workbook.

      As an alternative to Excel Ctrl + F window, this userform can be used. In this template ; unlike from data searching with Ctrl + F, the found results are saved to the created a report sheet(ReportSheet).

Also ,for the found results, are created hyperlinks when they are clicked that gone to cell address with this procedure :

Sub Create_Hyperlinks()
Dim LArray() As String, a As Long
For a = 2 To Sheets("ReportSheet").Cells(Rows.Count, 1).End(xlUp).Row
     ActiveSheet.Hyperlinks.Add Anchor:=Range(Cells(a, 1), Cells(a, 2)), Address:="", SubAddress:=Cells(a, 1) & "!" & Cells(a, 1).Offset(0, 1)
    Next a
 End Sub

➥ Firstly, when the userform opens, the sheets of the workbook are automatically listed in the drop-down list (VBA combobox control) :

Dim i As Byte
UserForm2.ComboBox1.AddItem "ALL SHEETS"
For i = 1 To Worksheets.Count
         UserForm2.ComboBox1.AddItem Worksheets(i).Name
    Next i

➥ Using textbox on userform ,data can be searched  in the selected sheet from the combobox or in all sheets as a part or as a whole.
The found results are listed on a listbox control. User can navigate between the items of the listbox by VBA spin button control .

Private Sub SpinButton1_SpinDown()
On Error Resume Next
If ListBox1.ListIndex = ListBox1.ListCount - 1 Then Exit Sub
With Me.ListBox1
        .ListIndex = .ListIndex + 1
    End With
 End Sub
Private Sub SpinButton1_SpinUp()
On Error Resume Next
If ListBox1.ListIndex = 0 Then Exit Sub
With Me.ListBox1
        .ListIndex = .ListIndex - 1
End With
End Sub

➥ When the selected item on the listbox is clicked,can be went to item's cell address.