Want to create a search gui on wkst1 to seach data on other wksts

[T. Londrigan]

I'm pretty sure if I was a VB developer this would be easy, but unfortunately
I'm not -- so any help would be appreciated.

I'd like to build a search gui on the first worksheet of a spreadsheet
containing only a text box for the user to type in the search string and a
pushbutton to submit. The data can be on any number of other worksheets
within the spreadsheet. I'd like to jump to the wkst to show the row of data
found with the matching text, then I guess the user can do findnext to
continue searching if needed.

Thanks,
Terri

[Bob Phillips]

Terri,

Here is some code. A couple of observations. It will search from the first
sheet forward. As you say, Find can be used to find any others on that
sheet, but not on another sheet, so you will need to run the second macro to
continue

Private ans

Sub FindString()
Dim sh As Worksheet
Dim oCell As Range
ans = InputBox("Input search string")
For Each sh In ActiveWorkbook
On Error Resume Next
Set oCell = sh.Cells.Find(ans)
On Error GoTo 0
If Not oCell Is Nothing Then
sh.Activate
oCell.Select
End If
Next sh
End Sub

Sub FindNextString()
Dim sh As Worksheet
Dim oCell As Range
Dim i As Long
For i = ActiveSheet.Index + 1 To Worksheets.Count
On Error Resume Next
Set oCell = sh.Cells.Find(ans)
On Error GoTo 0
If Not oCell Is Nothing Then
sh.Activate
oCell.Select
End If
Next i
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"T. Londrigan" <T. Londrigan@discussions.microsoft.com> wrote in message
news:45CFCEAE-420A-478A-A443-A348CAC7FD9F@microsoft.com...
> I'm pretty sure if I was a VB developer this would be easy, but
unfortunately
> I'm not -- so any help would be appreciated.
>
> I'd like to build a search gui on the first worksheet of a spreadsheet
> containing only a text box for the user to type in the search string and a
> pushbutton to submit. The data can be on any number of other worksheets
> within the spreadsheet. I'd like to jump to the wkst to show the row of
data
> found with the matching text, then I guess the user can do findnext to
> continue searching if needed.
>
> Thanks,
> Terri