Ways to expand search bar code? Thanks!!!

[Kamal1234]

Hi all,

I have made a search bar on a userform that will be used for finding data values across the workbook from different worksheets.

So far the search bar can only find values from "Sheet3". I want to modify the code so it can pick up data values from all worksheets in the workbook,

The search bar pickup values regarding to range and displays each row of data within 15 different TxtBoxes.

Below is the code I have used and a screenshot of my user form.

Private Sub btnSearch_Click()
Dim ws As Worksheet
Dim strSearch As String
Dim aCell As Range, v

On Error GoTo Err

'validate text box
v = Trim(TextBox6.Value)
If Len(v) = 0 Then
    MsgBox "Please Enter Data Value."
    Cancel = True
    Me.TextBox6.SetFocus
    Exit Sub
End If

Set aCell = Sheets("sheet3").Range("A:A").Find(v, lookat:=xlWhole)

If Not aCell Is Nothing Then

    With aCell.EntireRow

        TextBox1.Text = .Cells(, "A").Value
        TextBox2.Text = .Cells(, "B").Value
        TextBox3.Text = .Cells(, "C").Value
        TextBox4.Text = .Cells(, "D").Value
        TextBox5.Text = .Cells(, "E").Value
        TextBox7.Text = .Cells(, "F").Value
        TextBox8.Text = .Cells(, "G").Value
        TextBox9.Text = .Cells(, "H").Value
        TextBox10.Text = .Cells(, "I").Value
        TextBox11.Text = .Cells(, "J").Value
        TextBox12.Text = .Cells(, "K").Value
        TextBox13.Text = .Cells(, "L").Value
        TextBox14.Text = .Cells(, "M").Value
        TextBox15.Text = .Cells(, "N").Value
 
       

    End With

Else
    MsgBox "Data Value Not Found."
    Cancel = True
    'frmSearchTest.TextBox6.Value = ""
    TextBox6.SetFocus
End If

Exit Sub

Err:
    MsgBox Err.Description

End Sub

[MarvinP]

Hi Kamal,

You need to expand your search to multiple worksheets using the Worksheet INDEX.
In VBA WorkSheets.Count is how many sheets you have and instead of "Sheets("Sheet3")" you need to loop through all your sheets. Something like:

Dim SheetCounter as Dounble
...
For SheetCounter = 3 to Sheets.Count
Set aCell = Sheets(SheetCounter).Range("A:A").Find(v, lookat:=xlWhole)
...
...
Next SheetCounter

ALSO you need to have your code in a MODULE that isn't behind a sheet or the userform as this will allow it to scope each sheet correctly. You have you Sub as Private and this might mean it can't look outside of the sheet module it is behind.

http://www.cpearson.com/excel/Scope.aspx

[shg]

You know that functionality is built into Excel?

Marvin, Marvin, Marvin -- where are your CODE tags?

[MarvinP]

@shg,

OK - code tags in place,,,

I hate sentences that start with "You Know" as much as I hate sentences that start with "Unfortunately"

What functionality are you speaking of that "is built into Excel"?

Note - as I get older it seems I know less and less. Is it there is much more to know or I'm loosing some of my marbles?

[shg]

I confess to only glancing at the form, and not reading the OP's code (or yours) at all, but ...

If you do Find, set the Within dropdown to Workbook, and press Find All, you get a nice list of all the places the search value was found.

>> I hate sentences that start with "You Know"

The comment was directed to the OP, young Marvin

[MarvinP]

@shg

young Marvin

I may have a few years on ya, shg (dob in 47 is me)

Also - I don't use VBA Find often and will need to lookup the parms. I don't see "Within.Workbook" as one of the arguments to the VBA Find Method....

After searching more you meant to use the Home Tab - Find and Select Icon - Find .. Which shows the Find and Replace Dialog where I find the Within Workbook stuff.

shg - I see now where you were directing me...

I didn't know the "Find All" button list was capturable into VBA. Is it?

[shg]

I didn't know the "Find All" button list was capturable into VBA. Is it?

It's not, and you also can't control the Sheet/Workbook selector. But my point was/is, why create a userform and write a bunch of code to do something that the UI already does just fine.

[MarvinP]

NOW I totally understand!! Thanks for clearing that up

I guess I just missed the OP's intent and that it is already built into the GoTo tool in Excel. (I'm still learning )