Help with userform and search function

Graham6 · #1 (**permalink**) 02-09-12, 10:07

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

JerryDal · #2 (**permalink**) 02-09-12, 14:47

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

Graham6 · #3 (**permalink**) 02-09-12, 17:39

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

JerryDal · #4 (**permalink**) 02-09-12, 18:55

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

Graham6 · #5 (**permalink**) 02-10-12, 06:37

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.

JerryDal · #6 (**permalink**) 02-10-12, 14:34

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

Graham6 · #7 (**permalink**) 02-11-12, 07:02

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

JerryDal · #8 (**permalink**) 02-11-12, 12:38

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

Graham6 · #9 (**permalink**) 02-12-12, 03:20

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

JerryDal · #10 (**permalink**) 02-12-12, 11:54

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.

Graham6 · #11 (**permalink**) 02-12-12, 12:15

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

Graham6 · #12 (**permalink**) 02-13-12, 03:38

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

JerryDal · #13 (**permalink**) 02-13-12, 12:13

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.

Graham6 · #14 (**permalink**) 02-13-12, 17:08

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

Graham6 · #15 (**permalink**) 02-13-12, 18:20

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

This database only has tree columns
Regards
Graham

Internet Services

Web Development

Digital Marketing

Consumer Tech