Help with populating a Userform

richanor

Active Member
Joined
Apr 8, 2006
Messages
291
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):

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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi,
Code looks like one of mine.

Assuming you copied the rest of the code from post then that code should not need altering - you need to create a search code that calls it.

Add following codes to your forms code page & see if does what you want

Code:
Private Sub RecordNumber_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(Me.RecordNumber.Text) > 0 Then Search Me.RecordNumber
End Sub


Private Sub ID_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(Me.ID.Text) > 0 Then Search Me.ID
End Sub


Sub Search(ByVal TextBox As Object)
    Dim SearchRng As Range, FindCell As Range


    Set SearchRng = ws.Range(ws.Columns(1), ws.Columns(2))
    
    Set FindCell = SearchRng.Find(TextBox.Text, LookIn:=xlValues, lookat:=xlWhole)
    If Not FindCell Is Nothing Then
        r = FindCell.Row - 1
        Navigate xlRows
    Else
        MsgBox TextBox.Text & Chr(10) & "Record Not Found", 48, "Not Found"
    End If


End Sub


You should now be able to enter value in either of your textboxes & double click - If value found the record should be displayed & your navigate buttons set at correct record.

Dave
 
Upvote 0
Hi Dave - you're right, it was your code - from a thread a couple of years ago I think! It must be like a child; you may not see them for a while, but you'll always recognise your own!

Thanks so much for your help - it seems to work fine, but at the moment I am getting a 'Run-time error 91: Object variable or With block variable not set'.

Do you have any idea what is causing this? The data is being pulled from a sheet call 'database'

Many thanks

Rich
 
Upvote 0
Like I said - I assumed that you copied all the code from the original post - clearly, something is different.

Show which line is showing the error?

Dave
 
Upvote 0
Sorry - should have told you that in the last message. It's giving the error on the line:

Code:
    Set SearchRng = ws.Range(ws.Columns(1), ws.Columns(2))

Thanks

Rich
 
Upvote 0
do these lines of code exist in your userform code page
in particular ws variable?

Rich (BB code):
Dim ws As Worksheet
Dim r As Long
Const StartRow As Long = 2




Private Sub UserForm_Initialize()
    Set ws = ThisWorkbook.Worksheets("Database")
    
    'start at first record
    Navigate Direction:=xlFirst
End Sub

Dave
 
Upvote 0
They exist, but the Name of the userform is Userform1, so its:

Code:
Dim ws As Worksheet
Dim r As Long
Const StartRow As Long = 2




Private Sub UserForm1_Initialize()
    Set ws = ThisWorkbook.Worksheets("Database")
    
    'start at first record
    Navigate Direction:=xlFirst
End Sub
 
Upvote 0
They exist, but the Name of the userform is Userform1, so its:

Rich (BB code):
Dim ws As Worksheet
Dim r As Long
Const StartRow As Long = 2




Private Sub UserForm1_Initialize()
    Set ws = ThisWorkbook.Worksheets("Database")
    
    'start at first record
    Navigate Direction:=xlFirst
End Sub

You MUST NOT rename that procedure

It MUST stay with it's original name shown in RED

Rich (BB code):
Private Sub UserForm_Initialize()
    Set ws = ThisWorkbook.Worksheets("Database")
    
    'start at first record
    Navigate Direction:=xlFirst
End Sub

Remove the 1 from the end - ensure that the worksheet name shown in RED is correct & try code again.


Dave
 
Last edited:
Upvote 0
Yes, that works great! Thanks. At the risk of looking stupid, why can you not rename that procedure?
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top