Search data using Userform and then scroll through matching records

I have a userform which is currently used to search for data using a person's surname and then displays all data for that match. All works OK except it finds the first instance of a matching surname only and I want to then scroll through (on the userform) other matching instances of that same surname using a next button (and previous to go back when needed) until I reach the exact match I am looking for. (I would be OK with the next button simply scrolling alphabetically through the data list once the searched surnames are exhausted. So if for example there are five "Smiths" followed alphabetically by Soper and so on then it does not matter that it continues onto Soper and not stopping at end of Smiths but keeps going as it were.) Once the appropriate record is found the user can update details on the form and save using an update button and exit form or clear and search for another record.

I have a search button and code that works but I cannot work out how to then get a next and previous button to work from that record displayed. (I do have next/previous buttons that operate but always revert to the first record and not from the displayed record.) Ideally I would also want the search to ignore upper/lower case so any matching record would be found regardless of user input of surname with capital letter or not. I have clear and close buttons that work fine.

Code I have for next is:

Private Sub CmdButtonNext_Click()
' next button
Dim lastrow As Long

lastrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row

If currentrow = lastrow Then
MsgBox "You have reached the last record in the database"
Exit Sub
End If

currentrow = currentrow + 1

TxtMemNo = Cells(currentrow, 1)
TxtTitle = Cells(currentrow, 2)
TxtFirstName = Cells(currentrow, 3)
TxtSurname = Cells(currentrow, 4)
TxtEmail = Cells(currentrow, 5)

End Sub

The back button operates in the same way except using "currentrow - 1"

The search button code is:

Private Sub CmdButtonSearch_Click()

Dim totRows As Long, i As Long

totRows = ThisWorkbook.Worksheets("Membership").range("A10").CurrentRegion.Rows.Count

For i = 10 To totRows
If Trim(Sheet2.Cells(i, 4)) = Trim(TxtSurname.Text) Then
TxtMemNo.Text = Sheet2.Cells(i, 1)
TxtTitle.Text = Sheet2.Cells(i, 2)
TxtFirstName.Text = Sheet2.Cells(i, 3)
TxtSurname.Text = Sheet2.Cells(i, 4)
TxtEmail.Text = Sheet2.Cells(i, 5)

Exit For

End If

Next i

End Sub

I am a newbie and learning fast but any help greatly appreciated (or a pointer in the right direction and I will carry on searching the internet/trying ideas for solutions). I am thinking maybe I should be using a FindNext command or somehow call the code for the next/back buttons from within the search sub, however, despite trying several options I haven't managed to solve it. Thanks in advance for all/any thoughts.

I would put a ListBox on the UserForm and populate that from the search. The user can then pick the required 'record' from the ListBox.

Quote:

---

Originally Posted by paulked

I would put a ListBox on the UserForm and populate that from the search. The user can then pick the required 'record' from the ListBox.

---

If I place a ListBox on the UserForm, I'd still need a search function as there are over 800 records to look through (plus a lot more fields than my example code suggested; I left out most out of "repetitive" code for simplicity). I will look into adding a search function to a ListBox but suspect I might end up with same dilemma. It will be non-technical people updating records so it needs to be "simple".

Thanks for your suggestion.

I guess I could also, look at adding two fields to search on i.e. surname and first name - the chances of having duplicate records in that case would be minimal. Not sure how to achieve that but a bit more research needed!

I haven't got much time today but have muddled this as an example:

PS I searched on email as these tend to be unique and I don't know how your MemNo is set up.

Thanks Paul. I myself have been too busy today to delve into the depths but plan to do so over the weekend, family matters allowing! I have taken a quick look at the code and can see how the search function is operating. Being a newbie, one thing confuses me in that search button code and perhaps you'd explain. You have declared a variable "sLst as string" but I cannot see where that variable is used. Is it a case of declaring and then deciding you didn't need it or am I missing something?

I have not fully got to grips with the code you show against the select button; you've introduced me to two new functions - split and unbound. I have started to read up what they mean/do but not had time to fully take in. I need to go back to that section.

Thanks for your help. Lots to mull over and I still need to adapt to suit to what I am doing. Since I have some 24 columns of data I suspect I also need to find a way to loop through to create the listbox results without typing everyone individually.

I may have further questions when I have taken it myself to the next stage and hopefully can ask again for some guidance if needed. Thanks.

I was rushing, sLst isn't needed!

Split puts all the words in ListBox1 string into the array aEmail. UBound is the last element of that array, ie the email address.

Just pick the cells from the columns to put in the list box that can identify the row you want. Typing everyone? No idea why you need to do that.

Sure thing, there are lots of people here willing to help.

Post your workbook, or a sample if it contains sensitive data, and I'll have a look tomorrow :thumb

Quote:

---

Originally Posted by paulked

Typing everyone? No idea why you need to do that.

---

Thanks Paul - still need to think things through fully but when you ask "typing everyone?" your code includes:

With Sheet2
If .Cells(i, 3) = TextBox1 Then
ListBox1.AddItem .Cells(i, 1) & " " & .Cells(i, 2) & " " & .Cells(i, 3) & " " & .Cells(i, 4) & " " & .Cells(i, 5)
c = c + 1
End If
End With

I have some 20 plus columns of data and was thinking how to avoid typing each out for each column "cells (i, [number])" right up to 20 plus. And ideally make it dynamic anyway so should another column be added the code recognises and adds automatically. That was where I was coming from.

Aha! So if I understand you correctly, you don't need all the data in the listbox to identify the record, so just select the cells you do need. eg First name, last name, Town, email address. Then the user can double click on that item in the listbox to get all the information on that person (instead of CommandButton3_Click() use ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) to run the code).

If you do want all the columns then:

Code:

---

Private Sub CommandButton1_Click()
' SEARCH button
    Dim lastrow As Long, i As Long, j As Long, c As Long, sLst As String
    lastrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
    ListBox1.Clear
    For i = 1 To lastrow
        With Sheet2
            If .Cells(i, 3) = TextBox1 Then
                For j = 1 To .Cells(1, Columns.Count).End(xlToLeft).Column
                    sLst = sLst & .Cells(i, j) & " "
                Next
                sLst = Left(sLst, (Len(sLst) - 1))
                ListBox1.AddItem sLst
                c = c + 1
                sLst = ""
            End If
        End With
    Next
    If c = 0 Then MsgBox "Not found"
End Sub

---

That's where sLst came from :whistle:

Excellent and thanks, I knew there would be a better method and yep I just felt that sLst variable had a purpose.