find in entire workbook and write to userform

[Legacy 361687]

Hi,

I want to replicate the search function from excel, but without going to the cell location when there is a value found.

So I started making a search function myself.
It works to the point that I can find a value in a selected cell, which is pointless.

This is my code

Private Sub Searchbutton_Click()
Dim txt As String
Dim find As Range
Dim name, date, kind, ordernumber, send As String

txt = findTxt 'textbox on userform

Set find = Selection.find(What:=txt, After:=ActiveCell, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
 
 
If find Is Nothing Then
    MsgBox ("Nothing found")
    Exit Sub
Else
    name = name & (zoek.Offset(0, 0).Value) & vbNewLine
    date = date & (zoek.Offset(0, 2).Value) & vbNewLine
    kind = kind & vbNewLine & (zoek.Offset(0, 3).Value) & vbNewLine
    ordernumber = ordernumber & (zoek.Offset(0, 4).Value) & vbNewLine
    send = send & (zoek.Offset(0, 6).Value) & vbNewLine
End If
UserForm1.TextBox1 = name
UserForm1.TextBox2 = date
UserForm1.TextBox3 = kind
UserForm1.TextBox4 = ordernumber
UserForm1.TextBox5 = send
UserForm1.Show
Unload Me

The part that doesn't work, is where the search has to find every value occurence in al the sheets in the workbook.
I did trie, but nothing worked

could someone help me with a working example that I can modify?

Thanks in advance

 

[ranman256]

instead of FIND, what about just filtering the data and see ONLY the found the records.

 

[Legacy 361687]

instead of FIND, what about just filtering the data and see ONLY the found the records.

That is not an option.
With filtering you would still need to check every worksheet.

 

[ranman256]

yes. filter across all sheets.

 

[Legacy 361687]

The search function is meant to look for values without leaving the worksheet I'm curently in. If I didn't mind changing worksheets I would use the regular search function of excel