Search Multiple Pages

[Faiek]

Hi I would like to create a search system where it searches a textbox for a value and searches multiple sheets to find that value and finds it selects the cell on the sheet.

 

[Datsmart]

I believe Joe Was built what you are asking about here:
http://www.mrexcel.com/board2/viewtopic.php?t=246025

 

[Domenic]

Since you haven't provide enough information, I'm going to assume the following...

1. The name of the TextBox is "TextBox1".
   
2. The TextBox is an ActiveX control.
   
3. The TextBox is located on Sheet1 of the active workbook.
   
4. The macro needs to search all worksheets within the active workbook, except Sheet1.

Try...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] wksSheet1 [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] wks [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] FoundCell [color=darkblue]As[/color] Range

    [color=darkblue]Set[/color] wksSheet1 = Worksheets("Sheet1")
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] wks [color=darkblue]In[/color] Worksheets
        [color=darkblue]If[/color] wks.Name <> wksSheet1.Name [color=darkblue]Then[/color]
            [color=darkblue]Set[/color] FoundCell = wks.Cells.Find(what:=wksSheet1.OLEObjects("TextBox1").Object.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
            [color=darkblue]If[/color] [color=darkblue]Not[/color] FoundCell [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                wks.Activate
                FoundCell.Select
                [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] wks
        
    MsgBox "Search term was not found...", vbExclamation
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[Faiek]

Thanks For your replies. Datsmart I'll try that and see if it works, and Dominic Sorry For the Lack Of Details, It's Pretty much a userform with a textbox and a button when the button is clicked it will search the value in the textbox across all the pages.

 

[Domenic]

In that case, try the following instead...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()

    [color=darkblue]Dim[/color] wks [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] FoundCell [color=darkblue]As[/color] Range

    [color=darkblue]For[/color] [color=darkblue]Each[/color] wks [color=darkblue]In[/color] Worksheets
        [color=darkblue]Set[/color] FoundCell = wks.Cells.Find(what:=Me.TextBox1.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
        [color=darkblue]If[/color] [color=darkblue]Not[/color] FoundCell [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            wks.Activate
            FoundCell.Select
            [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] wks
    
    [color=darkblue]With[/color] Me.TextBox1
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Value)
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    MsgBox "Search term was not found...", vbExclamation
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[Faiek]

In that case, try the following instead...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()

    [color=darkblue]Dim[/color] wks [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] FoundCell [color=darkblue]As[/color] Range

    [color=darkblue]For[/color] [color=darkblue]Each[/color] wks [color=darkblue]In[/color] Worksheets
        [color=darkblue]Set[/color] FoundCell = wks.Cells.Find(what:=Me.TextBox1.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
        [color=darkblue]If[/color] [color=darkblue]Not[/color] FoundCell [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            wks.Activate
            FoundCell.Select
            [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] wks
    
    [color=darkblue]With[/color] Me.TextBox1
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Value)
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    MsgBox "Search term was not found...", vbExclamation
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

Thanks Domenic, It works perfect theres just one thing I want to change, How can I choose which pages it searches instead of it searching all of them?

 

[Domenic]

Try...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()

    [color=darkblue]Dim[/color] wks [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] FoundCell [color=darkblue]As[/color] Range

    [color=darkblue]For[/color] [color=darkblue]Each[/color] wks [color=darkblue]In[/color] Worksheets
        [color=darkblue]Select[/color] [color=darkblue]Case[/color] wks.Name
            [color=darkblue]Case[/color] "Sheet2", "Sheet3", "Sheet4"  [color=green]'change/add as desired[/color]
                [color=darkblue]Set[/color] FoundCell = wks.Cells.Find(what:=Me.TextBox1.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
                [color=darkblue]If[/color] [color=darkblue]Not[/color] FoundCell [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                    wks.Activate
                    FoundCell.Select
                    [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
                [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]Select[/color]
    [color=darkblue]Next[/color] wks
        
    MsgBox "Search term was not found...", vbExclamation
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]