Wednesday, July 27, 2016

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.



The following template can be used to search only in first sheet :

Searching Accross A Worksheet

No comments:

Post a Comment