Searching A Value Across An Entire Workbook With Textbox And Option Buttons

            The searched value can be easily found in all the workbook with the textbox and option buttons that they are added to the workbook's first sheet .


Through option buttons,value in textbox  can be searched as two types , whole or part :
If Sheets(1).OptionButton1 = True Then
Set Firstcell = Cells.Find(What:=Sheets(1).TxtSearch, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
Else
Set Firstcell = Cells.Find(What:=Sheets(1).TxtSearch, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
End If

All results are listed as hyperlinks in the generated report sheet with different a function.This function's codes :
Function NewSheet(argCreateList)
For Each Worksheet In ThisWorkbook.Worksheets
If argCreateList = Worksheet.Name Then
Exit Function ' if found - exit function
End If
Next Worksheet
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = argCreateList
End Function

User can be reached to cells of results when clicked on this hyperlinks :
Sub Create_Hyperlinks()
Dim LArray() As String
For Each cell In Sheets("New_Report").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
If cell <> "" Then
LArray = Split(cell.Text, "!")
ActiveSheet.Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:=LArray(0) & "!" & LArray(1)
End If
Next cell
End Sub


         The second row were excluded from to the searching ,because of there are the column headings in the second row.

When the result cells are active , the background color of these cells is yellow.





Also ,the following template can be used to search only in first sheet :

No comments:

Post a Comment