Excel Search

[FayeSaunders]

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

[Eri523]

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?

[FayeSaunders]

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

[Eri523]

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.

[FayeSaunders]

Sorry I am a bit of a newbie and this is like a foreign language. could you explain a bit more please?

[Eri523]

Ok, here's some code to get you started:

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.