Excel Search Box 2

[JeffITman]

Is there a way to place a text box on a sheet and use it as a search box for said sheet?

I have a 900 line sheet and would like to allow my employees to type in an exact number and have the cursor automatically go to the line that the number is on.
the user will open up to sheet 1. Within sheet 1 are 900 lines of information. The first Column (A) has a unique number that none of the other 899 lines have. The user will know which number they are looking for, i just though that i would make it easier to have a "find" field at the top of the sheet rather than the user having to open up that feature.

 

[SkipVought]

Hi,

You cannot put a TextBox on a sheet.

However, you could insert a Userform with a textbox and button.

Skip,

_[red][/red]

 

[misscrf]

why not turn on autofilter and let them choose from the filter?

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[JeffITman]

Skip,

How would I then have the textbox and button search through the sheet for the number that i'm looking for?

 

[SkipVought]

Ever use Edit/Find?

Skip,

_[red][/red]

 

[JeffITman]

Yea, but I wanted to make something easier for our employees. Sometimes they're not the brightest crayon in the box. Is there any what to maybe add a "search" button and when it's pressed, it calls Find?

 

[SkipVought]

Isn't that the idea?

Skip,

_[red][/red]

 

[JeffITman]

Skip, seriously. Could you please explain to me how to add a VB or USER button that once pressed, calls find. I would like to put this on a freeze panel at the top of the sheet.

 

[SkipVought]

turn on your macro recorder and record finding something.

Modify the macro to the textbox value rather than the value you entered.

Call the macro from your button click event.

Skip,

_[red][/red]

 

[SkipVought]

Post back with your reorded code for some help.

Skip,

_[red][/red]

 

[mintjulep]

It seems like the existing Name Box could be exploited for this. The Name Box is the text box to the left of the formula box in the standard Excel layout.

Its purpose in life is to tell you what cell you are in, or to let you quickly go to a specific cell or named range.

Use Insert | Name | Create

to make your existing cell contents into range names.

Type the range name into the name box.

You're there.

 

[misscrf]

Going with what Skip is suggesting, why not just put the find option onto a toolbar. If it is that hard for users to click the find, should they really be doing the work? lol
I run into the same problem with access apps that I create, constantly trying to make them as simple as possible. Sometimes,though, you have to demand some intelligence when using a software program.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[SkipVought]

Bravo misscrf ==>*

A SIMPLE, BUILT-IN solution!

Skip,

_[red][/red]

 

[misscrf]

lol, I had to find one sooner or later!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[JeffITman]

I understand misscrf. Unfortunately it's something that has been asked of me by a higher power.

Here's the code. If I place a number into "what", it works perfectly. How can I reference Textbox 1 in the "what"?

Cells.Find(What:="TextBox1", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate

 

[JeffITman]

OK. I figured that out. Is there a way that i could tell it to only search Column A?

 

[misscrf]

You have SearchOrder:=xlByRows

What if you put SearchOrder:=xlByColumns?

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[SkipVought]

Select the column and then Find

Skip,

_[red][/red]

 

[JeffITman]

Here's my code...

Private Sub CommandButton1_Click()
 Cells.Find(What:=TextBox1, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate

Is there anything i can put in there that will allow a pop up to come up if the information entered into the textbox1 is not found?

 

[PHV]

Perhaps something like this ?
Private Sub CommandButton1_Click()
Dim X As Range
Set X = Cells.Find(What:=TextBox1, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If X Is Nothing Then
MsgBox "the information entered into the textbox1 is not found"
Else
X.Activate
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886