Search form on work sheet

[Ryan_Bernal]

Hello!
Can somebody help me on this?
I am having hard time to populate text box without using input form.
I want to use input form on worksheet which i used to search data on my worksheets (database 2010,database 2011, database 2012.. etc)
Here is my code using input box which i pasted on standard module.

Public DSO As Object
Public DstRow As Long
Public DstWks As Worksheet
Public SrcWks As Worksheet

Private Sub FindKeyword(ByVal Keyword As String)

  Dim LastRow As Long
  Dim Result As Range
  Dim Rng As Range
  Dim StartRow As Long
    
    StartRow = 2
    LastRow = SrcWks.Cells(Rows.Count, "B").End(xlUp).Row
    LastRow = IIf(LastRow < StartRow, StartRow, LastRow)
    
      Set Rng = SrcWks.Cells(1, 1).CurrentRegion.Offset(1, 0)
      Set Rng = Rng.Resize(Rng.Rows.Count - 1)
      
        Set Result = Rng.Find(What:=Keyword, _
                              After:=Rng.Cells(1, 1), _
                              LookIn:=xlValues, _
                              LookAt:=xlPart, _
                              SearchOrder:=xlByColumns, _
                              SearchDirection:=xlNext, _
                              MatchCase:=False)
       A = Rng.Address
       If Not Result Is Nothing Then
         FirstAddx = Result.Address
           Do
             If Not DSO.Exists(Result.Row) Then
               DSO.Add Result.Row, DstRow
               SrcWks.Rows(Result.Row).EntireRow.Copy Destination:=DstWks.Cells(DstRow, "A")
               DstRow = DstRow + 1
             End If
             DstWks.Cells(DSO(Result.Row), Result.Column).Interior.ColorIndex = 6
             Set Result = Rng.FindNext(Result)
           Loop While Not Result Is Nothing And Result.Address <> FirstAddx
       End If
      
End Sub

Public Sub FindKeywords()

  Dim Keys As Variant
  Dim Keyword As Variant
  Dim Keywords As Variant
  Dim Msg As String
  Dim R As Variant
  
    Set DstWks = Worksheets("View")
    Set SrcWks = Worksheets("Database 2010")

    If DSO Is Nothing Then
      Set DSO = CreateObject("Scripting.Dictionary")
      DSO.Comparemode = vbTextCompare
    End If
    
      Msg = "Please enter the keywords below. Separate multiple entries with a comma." _
          & " Wildcard characters * and ? can also be included."
    
      Keywords = InputBox(Msg)
    
        If Keywords <> "" Then
          DstRow = 21
          DstWks.UsedRange.Offset(20, 0).Clear
          Keywords = Split(Keywords, ",", Compare:=vbTextCompare)
            For Each Keyword In Keywords
              FindKeyword (Keyword)
            Next Keyword
        Else
          Exit Sub
        End If
      
    Set DSO = Nothing
    Set Keywords = Nothing
    Sheets("View").Select
    Range("a21").Select
End Sub

Here is my work book:
Thank you in Advance!

[Admin]

Hi

Please find attached.

[Ryan_Bernal]

Thanks Admin!
The code you made is perfect!
I add new lines in workbook code so that i can use the search engine and add data form in one sheet.
here is the new code:

Private Sub Workbook_Open()
    Dim x
    
    x = GETSHEETS(Array("view"))
    Worksheets("view").cmbSheetName.List = x
    Worksheets("view").cmbSearchName.List = x
    
    
End Sub

By the way is it possible to add link on the search results so that when i click, i will go to the exact location of the searched data?

[Admin]

Hi

Thanks for the feedback.

It is possible to add link. Try to search on Hyperlinks.add in the VB help files (Search within VBE window). Record macro while adding the hyperlinks and customise the code.

BTW, even after the whole row is copied to the result sheet, why do you need to see the results in exact location ?

[Ryan_Bernal]

Thanks for the suggestions.
I am planning to make search engine for my folder directory that will output all the files in excel sheet with their links.
This is like the search command on windows, but this time using excel.
Happy New year!

[Ryan_Bernal]

Hello Admin,
Just want to know how to include another text boxes (criteria). So that i can filter only the data that i want base on the criteria.