Creating a Search Field?

[woodman650]

Hey all,
I was just wondering if there isn't a way to create a search field on a worksheet? Instead of opening up a "find" command, you just have a text input, you click a button and the object is highlighted on the page. Maybe this can be done with macros? Anyone know? thanks a bunch

[kassie]

Insert this code in your Worksheet section. Right click on the sheet name
tab, and select view code.

Private Sub cmdSearch_Click()

Dim vWhat As Variant
On Error GoTo A
vWhat = InputBox("Enter value to search for", "Enter search criteria")
Cells.Find(What:=vWhat, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
A:
MsgBox "String was not found"
Exit Sub
End Sub

In a strategic place on your worksheet, insert a command button, name it
cmdSearch.

Exit design mode, click on the button and an inputbox will appear where you
can enter your search string. When you click on OK, it will find it

"woodman650" wrote:

>
> Hey all,
> I was just wondering if there isn't a way to create a search field on a
> worksheet? Instead of opening up a "find" command, you just have a text
> input, you click a button and the object is highlighted on the page.
> Maybe this can be done with macros? Anyone know? thanks a bunch
>
>
> --
> woodman650
> ------------------------------------------------------------------------
> woodman650's Profile: http://www.excelforum.com/member.php...o&userid=26217
> View this thread: http://www.excelforum.com/showthread...hreadid=548037
>
>

[woodman650]

hi kassie,
I'm not familiar with adding buttons in excel... how do I add a command button? it sounds simple enough, but I don't know where to look. thanks

[kassie]

Had a lapsus latina!

Add
Exit sub just before A:, or then just afterthe last .Activate

"kassie" wrote:

> Insert this code in your Worksheet section. Right click on the sheet name
> tab, and select view code.
>
> Private Sub cmdSearch_Click()
>
> Dim vWhat As Variant
> On Error GoTo A
> vWhat = InputBox("Enter value to search for", "Enter search criteria")
> Cells.Find(What:=vWhat, After:=ActiveCell, LookIn:=xlFormulas, _
> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False, SearchFormat:=False).Activate
> A:
> MsgBox "String was not found"
> Exit Sub
> End Sub
>
> In a strategic place on your worksheet, insert a command button, name it
> cmdSearch.
>
> Exit design mode, click on the button and an inputbox will appear where you
> can enter your search string. When you click on OK, it will find it
>
> "woodman650" wrote:
>
> >
> > Hey all,
> > I was just wondering if there isn't a way to create a search field on a
> > worksheet? Instead of opening up a "find" command, you just have a text
> > input, you click a button and the object is highlighted on the page.
> > Maybe this can be done with macros? Anyone know? thanks a bunch
> >
> >
> > --
> > woodman650
> > ------------------------------------------------------------------------
> > woodman650's Profile: http://www.excelforum.com/member.php...o&userid=26217
> > View this thread: http://www.excelforum.com/showthread...hreadid=548037
> >
> >

[woodman650]

ahh, i got it working! is there a way to "embed" the form into the doc though... so it's ready to go? the text field is already there... etc.

[kassie]

Awfully sorry, I clean forgot about the button part!

Activate your control toolbox - Tools|Customize, and under toolbars ensure
that control toolbox is ticked, then click on close.

Click on the button icon, then doubleclick anywhere on your screen, where
you want the button to appear.

Right click on the button, select Properties, set the button name to
cmdSearch, and the Caption to Search or Find.

Exit Edit mode by clicking on the first icon on the toolbar - the one with
the ruler, triangle and pencil.

If you now click on the button, it will ask you what you want to search for.

Enjoy!

"woodman650" wrote:

>
> could an example file be posted possibly?
>
>
> --
> woodman650
> ------------------------------------------------------------------------
> woodman650's Profile: http://www.excelforum.com/member.php...o&userid=26217
> View this thread: http://www.excelforum.com/showthread...hreadid=548037
>
>