Creating a Search Field?
 

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

Creating a Search Field?
 

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

Creating a Search Field?
 

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

Creating a Search Field?
 

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


--
woodman650
------------------------------------------------------------------------
woodman650's Profile: http://www.excelforum.com/member.php...o&userid=26217
View this thread: http://www.excelforum.com/showthread...hreadid=548037

Creating a Search Field?
 

haha, I'm confused. So I just add "Exit Sub" before "A:" and after
".Acivate"?... Nothing happens. Should a button be appearing in my
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 Sub


--
woodman650
------------------------------------------------------------------------
woodman650's Profile: http://www.excelforum.com/member.php...o&userid=26217
View this thread: http://www.excelforum.com/showthread...hreadid=548037

Creating a Search Field?
 

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

Creating a Search Field?
 

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.


--
woodman650
------------------------------------------------------------------------
woodman650's Profile: http://www.excelforum.com/member.php...o&userid=26217
View this thread: http://www.excelforum.com/showthread...hreadid=548037

Creating a Search Field?
 

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