Results 1 to 10 of 10

Search Function Using Input Textbox

Threaded View

tommib Search Function Using Input... 05-20-2009, 05:44 AM
tommib Re: Changing my Search... 05-20-2009, 10:19 AM
T-J Re: Search Function Using... 05-20-2009, 02:59 PM
tommib Re: Search Function Using... 05-21-2009, 04:36 AM
T-J Re: Search Function Using... 05-21-2009, 10:23 AM
sweetrevelation Re: Search Function Using... 05-28-2009, 06:44 AM
  1. #1
    Registered User
    Join Date
    08-04-2008
    Location
    coventry
    Posts
    8

    Search Function Using Input Textbox

    Hi Guys.

    At the moment, I have a search spreadsheet where I click a button, and an input box appears where I put a car reg in, and it searches a different sheet, returning results on the row the reg belongs in.

    I've had a slight change in spec. I now need the same function to work, but instead of an input box.. I want the user to type the reg in a text box that is already on the sheet, and then click a search button to retrieve the results.

    Is there anyway to easily amend the following code to get there?


    Sub Button1_Click()
    'Sheet module code.
    'Find my data in the indicated range, on this sheet!
    Dim strMessage$, strTitle$, strDefault$, strShtNm$
    Dim lngMyCol&, lngLabelRow&
    Dim varRegID As Variant
    Dim rngMyData As Range
    Dim cell As Object
    
    Application.ScreenUpdating = False
    
    strShtNm = ActiveSheet.Name
    strMessage = "Enter ""Reg"" ID, below:" ' Set Prompt.
    strTitle = "Find This Information!" ' Set Title.
    strDefault = "" ' Set Default.
    
    ' Display strMessage, strTitle, and strDefault value.
    varRegID = InputBox(strMessage, strTitle, strDefault)
    'The row that your data's labels are in!
    lngLabelRow = 2
    'Change below to your range, the Column to find data in!
    With ThisWorkbook.Sheets("Sheet1")
      lngLstRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
    ThisWorkbook.Sheets("Sheet1").Activate
    ThisWorkbook.Sheets("Sheet1").Range("A2:A" & lngLstRow).Activate
    On Error GoTo myErr
    'Find the data!
    Selection.Find(What:=varRegID, _
    After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    'Get row data!
    lngMyCol = ActiveCell.Column
    lngMyRow = ActiveCell.Row
    lngLstCol = Sheets("Sheet1").Cells(lngMyRow, Columns.Count).End(xlToLeft).Column
    Set rngMyData = ThisWorkbook.Sheets("Sheet1").Range(Cells(lngMyRow, 1), Cells(lngMyRow, lngLstCol))
    For Each cell In rngMyData
    lngLabelCol = lngLabelCol + 1
    ThisWorkbook.Sheets("Sheet2").Cells(16, lngLabelCol).Value = cell.Value
    Next cell
    Sheets("Sheet1").Range("A1").Activate
    Sheets(strShtNm).Select
    
    GoTo myEnd
    myErr:
    Sheets("Sheet1").Range("A1").Activate
    Sheets(strShtNm).Select
    MsgBox "The ""Reg"" ID:" & vbLf & vbLf & _
    varRegID & vbLf & vbLf & _
    "was not found!", _
    vbCritical + vbOKOnly, _
    "Search Error!"
    myEnd:
    Application.ScreenUpdating = True
    End Sub
    Thanks in advance!!
    Last edited by tommib; 05-20-2009 at 11:28 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1