Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search field? | Excel Discussion (Misc queries) | |||
Text to Columns Question | Excel Worksheet Functions | |||
how can I search a field for a particular word | Excel Worksheet Functions | |||
FAQ Spreadsheet with search function | Excel Discussion (Misc queries) | |||
Creating a new field | Excel Discussion (Misc queries) |