CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6

Thread: Excel Search

Hybrid View

  1. #1
    Join Date
    Jan 2013
    Posts
    3

    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

  2. #2
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,675

    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?
    I was thrown out of college for cheating on the metaphysics exam; I looked into the soul of the boy sitting next to me.

    This is a snakeskin jacket! And for me it's a symbol of my individuality, and my belief... in personal freedom.

  3. #3
    Join Date
    Jan 2013
    Posts
    3

    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

  4. #4
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,675

    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.
    I was thrown out of college for cheating on the metaphysics exam; I looked into the soul of the boy sitting next to me.

    This is a snakeskin jacket! And for me it's a symbol of my individuality, and my belief... in personal freedom.

  5. #5
    Join Date
    Jan 2013
    Posts
    3

    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?

  6. #6
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,675

    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.
    Attached Images Attached Images  
    Attached Files Attached Files
    Last edited by Eri523; February 2nd, 2013 at 12:19 PM. Reason: Removed a forgotten piece of debugging code from the code and uploaded updated ZIP
    I was thrown out of college for cheating on the metaphysics exam; I looked into the soul of the boy sitting next to me.

    This is a snakeskin jacket! And for me it's a symbol of my individuality, and my belief... in personal freedom.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured