krackers
12-05-2019, 04:30 PM
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 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.