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.


  1. This comment has been removed by the author.

  2. When pressing search button the following error shows : Compile error :Can't find project or library. Any suggestions?

  3. Is there an incomplete component in your excel version (in VBE- Visual Basic Editor)?