Help with populating a Userform

[richanor]

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):

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

 

[richanor]

ooooh - very helpful, and interesting. Thanks for the pointer on that!

Rich