Hi, I have a basic multipage userform which is setup to add a new record and write the contents back to a database sheet when the update command button is clicked. Also, the user can recall/amend records by cycling through using the next/previous buttons. The next / previous button run the following code (not written by me - taken from a post in this forum):
The problem is that there are now over 100 records and it is taking ages to cycle through them all!
There are 2 text boxes on the userform (RecordNumber and ID) which write to columns A and B on the 'database' sheet respectively. If the user enters a value in either of these text boxes, is there a way to amend the code so that instead of starting at row 1, it searches column A or B (depending on which textbox has been filled) for a matching value and starts at that row instead?
Not sure whether this is clear at all - please tell me if not!
Many thanks in advance
Rich
Code:
Sub Navigate(ByVal Direction As XlSearchDirection)
Dim i As Integer
Dim Lastrow As Long
Set ws = ThisWorkbook.Worksheets("Database")
Lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
r = IIf(Direction = xlPrevious, r - 1, r + xlNext)
'ensure value of r stays within data range
If r < StartRow Then r = StartRow
If r > Lastrow Then r = Lastrow
'get record
For i = 1 To UBound(ControlNames)
Me.Controls(ControlNames(i)).Text = IIf(Direction = xlNone, "", ws.Cells(r, i).Text)
Next i
'set enabled status of next previous buttons
Me.NextRecord.Enabled = IIf(Direction = xlNone, False, r < Lastrow)
Me.PrevRecord.Enabled = IIf(Direction = xlNone, False, r > StartRow)
End Sub
The problem is that there are now over 100 records and it is taking ages to cycle through them all!
There are 2 text boxes on the userform (RecordNumber and ID) which write to columns A and B on the 'database' sheet respectively. If the user enters a value in either of these text boxes, is there a way to amend the code so that instead of starting at row 1, it searches column A or B (depending on which textbox has been filled) for a matching value and starts at that row instead?
Not sure whether this is clear at all - please tell me if not!
Many thanks in advance
Rich