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
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) :
➥ 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 .
➥ When the selected item on the listbox is clicked,can be went to item's cell address.
Dim i As Byte
UserForm2.ComboBox1.AddItem "ALL SHEETS"
For i = 1 To Worksheets.Count
UserForm2.ComboBox1.AddItem Worksheets(i).Name
Next i
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
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.
Read more ...