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
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.
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
endRow = ActiveCell.Row
Application.ScreenUpdating = True
Set searchRange = Range("A2:D" & endRow)
foundTarget = True
Set LastCell = .Cells(.Cells.Count)
Set FoundCell = searchRange.Find(what:=searchText, after:=LastCell)
If Not FoundCell Is Nothing Then
FirstAddr = FoundCell.Address
foundTarget = False
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
i = i + 1
If Not foundTarget Then
MsgBox "No data found for " & searchText
Me.txtTarget.Text = ""
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.
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.
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:
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
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.
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
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
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
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.
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")