Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Feb 2012
    Posts
    19

    Red face Unanswered: Help with userform and search function

    I have a wookbook with data from A2 to E300, I have a userform with a textbox to enter the word to search for. a command button to start the search, and a listbox to get search results. I also could have the search word 6 times in the wookbook so I want to search also for next same entered word. But if I can get it to search and return info that would be a great start I have tried for days to do this without any luck, also using CTRL+f

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    This code may help with your solution. A list box is loaded with all instances of the found target.
    An Excel 2003 file is attached.
    Code:
    Private Sub cmdFind_Click()
    'search for searchText and populate list box with all data found
    Dim searchText As String, FirstAddr As String
    Dim FoundCell As Range, LastCell As Range, searchRange As Range
    Dim i As Long, endRow As Long
    Dim foundTarget As Boolean
    
        searchText = Me.txtTarget.Text
    
        If Len(searchText) = 0 Then Exit Sub
       
        Application.ScreenUpdating = False
        Range("A1").End(xlDown).Select
        endRow = ActiveCell.Row
        Range("A1").Select
        Application.ScreenUpdating = True
        
        Set searchRange = Range("A2:D" & endRow)
        
        Me.lstCustSearch.Clear
        foundTarget = True
        
        With searchRange
            Set LastCell = .Cells(.Cells.Count)
        End With
    
        Set FoundCell = searchRange.Find(what:=searchText, after:=LastCell)
    
        If Not FoundCell Is Nothing Then
            FirstAddr = FoundCell.Address
        Else
            foundTarget = False
        End If
    
        i = 0
        Do Until FoundCell Is Nothing
    
            Me.lstCustSearch.AddItem Cells(FoundCell.Row, 1).Value
            Me.lstCustSearch.List(i, 1) = Cells(FoundCell.Row, 2).Value
            Me.lstCustSearch.List(i, 2) = Cells(FoundCell.Row, 3).Value
            Me.lstCustSearch.List(i, 3) = _
                Format(Cells(FoundCell.Row, 4).Value, "$#,##0.00")
            
            Set FoundCell = searchRange.FindNext(after:=FoundCell)
            If FoundCell.Address = FirstAddr Then
                Exit Do
            End If
            i = i + 1
            
        Loop
        
        If Not foundTarget Then
            MsgBox "No data found for " & searchText
        Else
            Me.txtTarget.Text = ""
        End If
        
        Me.txtTarget.SetFocus
        
    End Sub
    Attached Files Attached Files

  3. #3
    Join Date
    Feb 2012
    Posts
    19

    Help

    Hi Jerry
    Can you let me know what Me.1stCustSearch.clear relates to please.I have just deleted it the pro runs but finds nothing
    Thanks Graham

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    After you view information in the list box, when you do another search,
    the statement Me.lstCustSearch.Clear clears out (empties) the list box just before the code populates the list box with new data for your next viewing.
    Jerry

  5. #5
    Join Date
    Feb 2012
    Posts
    19

    Expand data

    Many thanks for the info, I have now managed to put data into the search box press commandbutton3 and away it goes, it then returns the some of the info I require.
    BUT I require the returned info to show the complete row of data. i.e. A34,B34,C34,D34,E34. If anyone can tell me how to change it so to display the row in the reply above, I would be very greatful.

  6. #6
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    My code returns columns A-D, which I assign to the range variable "searchRange".
    I sometimes try to offer a solution that encourages learning, such as giving a 4-column solution
    to a 5-column problem. Then again, I sometimes miss-read the problem. I won't say which was the case here.

    If you used this code as a model, you would change "D" to "E" in your application.
    Then you would add code for a 5th item to be added to your 5-column list box with code
    similar to this:

    Code:
    Me.lstCustSearch.List(i, 4) = Cells(FoundCell.Row, 5).Value
    The above code changes would work with my demo if the list box in the form were edited and
    a 5th column added, and you would see the results if test data was added to Sheet1 column E.
    Jerry

  7. #7
    Join Date
    Feb 2012
    Posts
    19

    unable to get userform to return row A6 to E6 etc

    Hi
    I have changed the data as above to TexBox6,listBox1, range A3:E1228, to work with my data. The other data as above has been added.
    But my returned data does not show the whole row ie A8,B8,C8,D8,E8.
    It just seems to return data from colum A1 to A1228
    Can you please help

  8. #8
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Edit your list box control to display the 5 columns A-E. You want it to be a 5-column list box.

    If you study the list box properties in my example file, you'll see that I made it a multi-column list box, and I assigned column widths to each column. You'll need to tweak your list box column width measurements to get the best display of your data. For example, you would use a narrow column width for zip code field and a wider column width for a client name field. The total of your 5 column widths should not exceed the width of the list box.

    If you have data wider than the list box can handle, consider increasing the width of the list box so you can have wider list box columns.
    Good luck.
    Jerry

  9. #9
    Join Date
    Feb 2012
    Posts
    19

    Many Thanks

    Hi Jerry
    All the database works great, I can't thank you enough for your help at 66 year this new stuff does not go in as fast.
    Just one last thing is it possible to load the userform from the Desk top icon
    Regards
    Graham

  10. #10
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Open your Excel workbook, press Alt+F11, insert a Module if you don't already
    have one, and add this code to it, changing the name of the user form UserForm1
    if your form has a different name.

    Code:
    Sub Auto_Open()
    
        Load UserForm1
        UserForm1.Show
        
    End Sub
    Save and close the workbook.
    Set up your Windows desktop icon to open your Excel file. The user form
    will open automatically every time the workbook is opened.

  11. #11
    Join Date
    Feb 2012
    Posts
    19

    Thanks

    Hi Jerry
    Many thanks all is working fine.I have used the data to document old places in my Town and to imform people where they are now. I hope it will help the people of the town. I have a website and the next step is to upload the data for the town to use (free). This job is a stand alone data but to do the same job
    Again Thanks

  12. #12
    Join Date
    Feb 2012
    Posts
    19

    change database to show one row at a time with next

    Is it posible to change the above code to show one row at a time with a commandbutton to do next find.
    Also one of the colmns has has a lot of data in it I would like to be returned into the ListBox. the other columns are just one line.
    Thanks this is my last project

  13. #13
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Post back a row of data, fictitious if you prefer, so I can get a sense of how wide the columns are.
    How many characters does the longest string of text have in the column with 'a lot of data'?
    Describe what each of the 5 fields represent and in what column order you would display them.
    Then I'll see if I can come up with something.

  14. #14
    Join Date
    Feb 2012
    Posts
    19

    Info I hope as requested

    Hi Jerry, Thanks again this is the last database, on historic info on my town back to 1100. The database has three fields, A=reference=20 characters, B=Document=350 words, C=date=10 characters. To have a command button, because I would like to recieve one row of data at a time, but the info I am searching for my have been found on more than one doc, that's why I think I require a next button.
    I am able to search for a word and columns A & C are returned. But I am having trouble with th B column.
    Debug has trouble with Set searchRange = Range("A2:C1626" & endRow) THIS IS MY RANGE.
    Also Me!ListBox1.AddItem Cells(FoundCell.Row, 1).Value
    Me!ListBox1.List(i, 1) = Cells(FoundCell.Row, ).Value
    Me!ListBox1.List(i, 2) = Cells(FoundCell.Row, 3).Value
    Me.ListBox1.List(i, 4) = _
    Format(Cells(FoundCell.Row, 4).Value, "$#,##0.00")
    Regards Graham

  15. #15
    Join Date
    Feb 2012
    Posts
    19

    error on previous post

    should have been
    Me!ListBox1.List(i, 1) = Cells(FoundCell.Row, 2).Value

    This database only has tree columns
    Regards
    Graham

Posting Permissions

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