Creating a Search Field?

W

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
 
K

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
 
K

kassie

Had a lapsus latina!

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

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
 
W

woodman650

haha, I'm confused. So I just add "Exit Sub" before "A:" and afte
".Acivate"?... Nothing happens. Should a button be appearing in m
worksheet?

so what I have now is:


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
Exit Sub
A:
MsgBox "String was not found"
Exit Sub
End Su
 
W

woodman650

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

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!
 
Top