-
Excel Search
I am trying to create a spreadsheet for work and they require it to have a search function exactly like Ctrl & F that sits within the worksheet. Is there a way I can do this?
They do not want to use Ctrl & F as it is for their staff so it requires a user interface similar to a webpage but cannot be accessed externally.
Not sure if I am writing in the correct place but can anyone help?
Thanks
-
Re: Excel Search
What do you mean by "sits within the worksheet"? Some sort of button embedded in the sheet that the user can click and then launches the search dialog?
-
Re: Excel Search
Yes, a search box in a cell for the user to type in with a button next to it thats launches the search
Is this possible?
Thanks
-
Re: Excel Search
Sure it's possible. :)
First you need a VBA macro that picks up the search term from the worksheet cell you chose for that purpose and initiates the search. For the search funtionality, the help page for the Range.Find method probably is the best place to start. You can use the Controls tool box to place a command button on your worksheet and connect that to the existing macro. (The Forms tool bar provides a command button to put on a worksheet too, but I think the one from the Controls tool bar is a bit easier to handle.)
Controlling Excel's search functionality from VBA can be rather tricky and you may need to do some stepwise refinement on your macro until you get what you want.
-
Re: Excel Search
Sorry I am a bit of a newbie and this is like a foreign language. could you explain a bit more please?
-
2 Attachment(s)
Re: Excel Search
Ok, here's some code to get you started:
Code:
Option Explicit
Sub CustomFind()
Dim strSearchTerm As String
strSearchTerm = ActiveSheet.Range("$E$5").Value
SelectSearchResult ActiveSheet.Cells.Find(strSearchTerm)
End Sub
Sub CustomFindNext()
SelectSearchResult ActiveSheet.Cells.FindNext(Selection)
End Sub
Private Sub SelectSearchResult(rngSearchResult As Range)
If Not rngSearchResult Is Nothing Then
If rngSearchResult.Address = "$E$5" Then ' Search result is search term field itself: ...
Set rngSearchResult = ActiveSheet.Cells.FindNext(rngSearchResult) ' ... skip it
End If
If rngSearchResult.Address = "$E$5" Then ' Hit search term field anagin: Term doesnt exist anywhere else
MsgBox "Search term not found"
Else
rngSearchResult.Select
End If
End If
End Sub
The first two Subs are connected to the two buttons placed on the demo spreadsheet by using the Forms tool bar. The macro code can be found in the ThisWorkbook node of the object tree displayed by the VBA editor.
The code listing as well as the attached screen shot of the demo sheet are mainly for reference and discussion, so there's no need to downoad the zipped Excel file I have attached as well, unless one (in particular the OP) actually wants to explore and play with it.
However, though not quite trivial, this is just a rudimentary implentation of an embedded find mechanism and you'll likely need to refine it to get what you want, as I already alluded.