Help with populating a Userform

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Help with populating a Userform

  1. #1
    Board Regular
    Join Date
    Apr 2006
    Posts
    262
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help with populating a Userform

    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

  2. #2
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    3,788
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with populating a Userform

    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

  3. #3
    Board Regular
    Join Date
    Apr 2006
    Posts
    262
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with populating a Userform

    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

  4. #4
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    3,788
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with populating a Userform

    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

  5. #5
    Board Regular
    Join Date
    Apr 2006
    Posts
    262
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with populating a Userform

    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

  6. #6
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    3,788
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with populating a Userform

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

    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

  7. #7
    Board Regular
    Join Date
    Apr 2006
    Posts
    262
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with populating a Userform

    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

  8. #8
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    3,788
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with populating a Userform

    Quote Originally Posted by richanor View Post
    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
    You MUST NOT rename that procedure

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

    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 by dmt32; Jun 28th, 2017 at 04:50 PM.

  9. #9
    Board Regular
    Join Date
    Apr 2006
    Posts
    262
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with populating a Userform

    Yes, that works great! Thanks. At the risk of looking stupid, why can you not rename that procedure?

  10. #10
    Board Regular
    Join Date
    Jul 2012
    Location
    Hampshire, UK
    Posts
    3,788
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help with populating a Userform

    Quote Originally Posted by richanor View Post
    Yes, that works great! Thanks. At the risk of looking stupid, why can you not rename that procedure?
    Simply because it's one of the built-in events for your userform - changing it's name as you discovered, renders it useless.

    Have read here:Sequence of events in Userforms • Pixcels.nl

    and see if helpful.

    Dave

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com