Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Creating a Search Box in Excel

This is a discussion on Creating a Search Box in Excel within the Excel Questions forums, part of the Question Forums category; Hey guys. I need some help in creating a search box in excel. I have 4 rows of data, and ...

  1. #1
    Board Regular
    Join Date
    Aug 2008
    Posts
    61

    Default Creating a Search Box in Excel

    Hey guys.

    I need some help in creating a search box in excel. I have 4 rows of data, and want the user to enter a 4 digit number in a text box, which is then searched in the spreadsheet, pulling up the results.

    So far I have the following code..

    Code:
    Sub Button1_Click()
         
         'Opens box and ask what do they want to  search
        searchthis = InputBox("Type in a location keyword.", "Property Search")
         'Tells where to search
        Columns("A:E").Select
         'and then search in them whatever the user entered:
        Selection.Find(What:=searchthis, After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext).Activate
    
    End Sub
    It only highlights the results, leaving all other data on the spreadsheet. Ideally id want to see only the results of the search. Is there anyway to do this?.

    Also, if the search returns no results, I get a debugger message. I want some sort of error message to come up.

    Please help!!

  2. #2
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default Re: Creating a Search Box in Excel

    It depends on what is OK for you?
    You can hide and un-hide rows, change font colors from transparent to viable, Move the user to a new sheet and only show a copy of the found data, hide everything and only display the found data in a UserForm or TextBox or MsgBox...

    What do you have in mind, for how you envision your application working?
    JSW: Try and try again: "The way of the Coder!"

  3. #3
    Board Regular
    Join Date
    Aug 2008
    Posts
    61

    Default Re: Creating a Search Box in Excel

    Ideally... Id want a text box in a spreadsheet.. and when a user enters data in the box and clicks a search button.. all instances of the text box is shown (the search string could be anywhere.. standing alone or imbedded within a string.. so for eg.. if someone searches for 123.. the result test123test also appears)

    At the moment.. Im having to click a button.. which gives a pop up prompting me to enter a string.. and when I search for this.. the result is highlighted amongst the data in the spreadsheet (all data is located below the search button)

    Hope this makes things a bit more clearer.

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,547

    Default Re: Creating a Search Box in Excel

    Why re-invent the wheel? That functionality is already built into Excel. From Edit - Find.

    You can specify to search the whole book, and to search partial strings.

    It displays all results found in a nice list, hyperlinked to each cell containing the string.
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default Re: Creating a Search Box in Excel

    How do you want the application to run?
    From a Navagation sheet with the data on another sheet?
    A Text box hard-coded static on a sheet?
    One Big TextBox or a Self-sizing one on its own sheet?
    Two TextBoxs one for the found data one for the search?
    Do you want the data location or the actual data or both?
    Is a custom UserForm better?
    Can you build a UserForm if we help with the code?
    JSW: Try and try again: "The way of the Coder!"

  6. #6
    Board Regular
    Join Date
    Aug 2008
    Posts
    61

    Default Re: Creating a Search Box in Excel

    Hi Joe.

    Ideally, Id like a text box on a blank worksheet.. and when the user enters text and clicks a search button.. the results are displayed under the text box (there will be more than 2 results per search).

    Just want it to be a bit error proof. I want the user to know when nothing has been retrieved (either in a form of an error message.. or just a blank sheet.. not having a debugger pop up)

    Hopefully this is a bit more clearer

  7. #7
    Board Regular
    Join Date
    Aug 2008
    Posts
    61

    Default Re: Creating a Search Box in Excel

    Can anyone help on this please

  8. #8
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default Re: Creating a Search Box in Excel

    Sub CommandButton1_Click()
    'Standard Sheet Module code, like: Sheet1.
    Dim iFound
    Dim Message$, Title$, Default$, myCode$

    Message = "Enter data to search for:" ' Set prompt.
    Title = "Get Data!" ' Set title.
    Default = "" ' Set default.
    ' Display message, title, and default value.
    myCode = InputBox(Message, Title, Default)

    iFound = False
    Application.ScreenUpdating = False

    'Check data sheet for data wanted!
    Worksheets("Sheet1").Select
    For Each r In Worksheets("Sheet1").UsedRange.Columns
    n = r.Column

    If Worksheets("Sheet1").Cells(1, n) = myCode Then
    iFound = True
    'Copy the found data from the starting row = myStart to
    'the ending row = myFinish for the Found Date [myDate]
    'to Sheet2 in column "C" change as needed!
    'Start data import just below any entery in this column!
    Worksheets("Sheet1").Range(Cells(2, n), Cells(4, n)).Copy _
    Destination:=Worksheets("Sheet2").Range("C65536").End(xlUp).Offset(1, 0)
    Else

    End If
    Next
    r

    If iFound = False Then MsgBox "Date not Found!"
    Worksheets("Sheet2").Select
    Application.CutCopyMode = True
    Application.ScreenUpdating = True
    End Sub
    JSW: Try and try again: "The way of the Coder!"

  9. #9
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539

    Default Re: Creating a Search Box in Excel

    This is a more advanced vertion of a search for and report found data macro:

    Sub myFind()
    'Standard module code, like: Module1.
    'Find my data and list found rows in report!

    Dim rngData As Object
    Dim strDataShtNm$, strReportShtNm$, strMySearch$, strMyCell$
    Dim lngLstDatCol&, lngLstDatRow&, lngReportLstRow&, lngMyFoundCnt&

    On Error GoTo myEnd
    '*******************************************************************************
    strDataShtNm = "Sheet2" 'This is the name of the sheet that has the data!
    strReportShtNm = "Sheet1" 'This is the name of the report to sheet!
    '*******************************************************************************
    Sheets(strReportShtNm).Select
    Application.ScreenUpdating = False

    'Define data sheet's data range!
    Sheets(strDataShtNm).Select

    With ActiveSheet.UsedRange
    lngLstDatRow = .Rows.Count + .Row - 1
    lngLstDatCol = .Columns.Count + .Column - 1
    End With

    Set rngData = ActiveSheet.Range(Cells(1, 1), Cells(lngLstDatRow, lngLstDatCol))

    'Get the string to search for!
    strMySearch = InputBox("Enter what to search for, below:" & vbLf & vbLf & _
    "Note: The search is case sensitive!", _
    Space(3) & "Find All", _
    "")

    'Do the search!
    For Each Cell In rngData
    strMyCell = Cell.Value

    'If found then list entire row!
    If strMyCell = strMySearch Then
    lngMyFoundCnt = lngMyFoundCnt + 1
    ActiveSheet.Rows(Cell.Row & ":" & Cell.Row).Copy

    With Sheets(strReportShtNm)
    'Paste found data's row!
    lngReportLstRow = .UsedRange.Rows.Count + .UsedRange.Row
    ActiveSheet.Paste Destination:=.Range("A" & lngReportLstRow).EntireRow
    End With
    End If
    Next
    Cell

    myEnd:
    'Do clean-up!
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    Sheets(strReportShtNm).Select

    'If not found then notify!
    If lngMyFoundCnt = 0 Then
    MsgBox """" & strMySearch & """" & Space(3) & "Was not found!", _
    vbCritical + vbOKOnly, _
    Space(3) & "Not Found!"
    End If
    End Sub
    JSW: Try and try again: "The way of the Coder!"

  10. #10
    Board Regular
    Join Date
    Nov 2008
    Posts
    207

    Default Re: Creating a Search Box in Excel

    Hi all,

    Sorry for cutting in on this thread but I'm looking for some code for a much simpler search box and I'm thinking Joe's solution if adapted might suit. I want to put a the box at the top of my worksheet with a 'Go' button alongside it. The Range to be searched is C21:C385. If a match is found I want the sheet to scroll to the match and make the cell active. If no match is found a message box should display with an appropriate message. Thanks.

Page 1 of 3 123 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com