UserForm Next Record Same Last Name

[LikeButtah1]

Hello All,
I am trying to find the next record of the same last name that the user will type into textbox1. I can have another button to navigate through the records but after finding the last record it should say "Last Record Found". Column A holds the last names and first record starts in A3. Here is the code that finds the first instance of the last name in textbox1:

Dim blnNew As Boolean
Dim TRows, i As Long




Private Sub btnLName_Click()


                blnNew = False
                txtLastName.Text = ""
                txtFirstName.Text = ""
                txtC.Text = ""
                txtNewCard.Text = ""
                txtAccDes.Text = ""
                txtAccCreated.Text = ""
                txtAccGranted.Text = ""
                txtAccCancelled.Text = ""
                txtAccCategory.Text = ""
                txtNotes.Text = ""
   
    TRows = Worksheets("PNG Database").Range("A1").CurrentRegion.Rows.Count
    For i = 3 To TRows
        If LCase(Worksheets("PNG Database").Cells(i, 1).Value) = LCase(TextBox1.Text) Then
                
            txtLastName.Text = Worksheets("PNG Database").Cells(i, 1).Value
            txtFirstName.Text = Worksheets("PNG Database").Cells(i, 2).Value
            txtC.Text = Worksheets("PNG Database").Cells(i, 3).Value
            txtNewCard.Text = Worksheets("PNG Database").Cells(i, 4).Value
            txtAccDes.Text = Worksheets("PNG Database").Cells(i, 5).Value
            txtAccCreated.Text = Worksheets("PNG Database").Cells(i, 6).Value
            txtAccGranted.Text = Worksheets("PNG Database").Cells(i, 7).Value
            txtAccCancelled.Text = Worksheets("PNG Database").Cells(i, 8).Value
            txtAccCategory.Text = Worksheets("PNG Database").Cells(i, 9).Value
            txtNotes.Text = Worksheets("PNG Database").Cells(i, 12).Value
         
            Exit For
        End If
    Next i
    
   


End Sub

Any help would be great. Thanks in advance.

 

[Fluff]

Rather than accessing the sheet all the time, how about adding a listbox to your form, that can then be populated with the contents of textbox1.
That way you can see all instances of the last name in one go.

Option Explicit
Private Dic As Object

Private Sub ListBox1_Click()
   With Me.ListBox1
      Me.[COLOR=#ff0000]tb2[/COLOR].Value = .List(.ListIndex, 0)
      Me.[COLOR=#ff0000]tb3[/COLOR].Value = .List(.ListIndex, 1)
      Me.[COLOR=#ff0000]tb4[/COLOR].Value = .List(.ListIndex, 2)
   End With
End Sub

Private Sub TextBox1_AfterUpdate()
   Me.ListBox1.List = Dic(Me.TextBox1.Value).Value
End Sub

Private Sub UserForm_Initialize()
   Dim Cl As Range
   Dim ws As Worksheet

   Set ws = Sheets("PNG Database")
   Set Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = vbTextCompare
   For Each Cl In ws.Range("A3", ws.Range("A" & Rows.Count).End(xlUp))
      If Not Dic.exists(Cl.Value) Then
         Dic.Add Cl.Value, Cl.Resize(, 12)
      Else
         Set Dic(Cl.Value) = Union(Dic(Cl.Value), Cl.Resize(, 12))
      End If
   Next Cl
End Sub

Then when you click on a line in the listbox, it will populate your textboxes. Change values in red to match your textbox names

 

[dmt32]

Hi,
untested but see if this update to your code does what you want

Dim TRows As Long


Private Sub btnLName_Click()
    Static StartRow As Long




    Set PNGDatabase = ThisWorkbook.Worksheets("PNG Database")


    If StartRow = 0 Then StartRow = 3
    With PNGDatabase
    TRows = .Range("A1").CurrentRegion.Rows.Count
    For i = StartRow To TRows
        If LCase(.Cells(i, 1).Value) = LCase(TextBox1.Text) Then
            txtLastName.Text = .Cells(i, 1).Value
            txtFirstName.Text = .Cells(i, 2).Value
            txtC.Text = .Cells(i, 3).Value
            txtNewCard.Text = .Cells(i, 4).Value
            txtAccDes.Text = .Cells(i, 5).Value
            txtAccCreated.Text = .Cells(i, 6).Value
            txtAccGranted.Text = .Cells(i, 7).Value
            txtAccCancelled.Text = .Cells(i, 8).Value
            txtAccCategory.Text = .Cells(i, 9).Value
            txtNotes.Text = .Cells(i, 12).Value
            StartRow = i + 1
            Exit Sub
        End If
    Next i
    End With
    StartRow = 0
    MsgBox "Last Record Found", 48, "End Of File"
    
End Sub

Each press of the button should display next occurrence of search record until msgbox displayed

Dave

 

[LikeButtah1]

Fluff,
I'm getting a Compile Error: Sub or Function not defined here:

Private Sub TextBox1_AfterUpdate()
Me.ListBox1.List = Dic(Me.TextBox1.Value).Value
End Sub

 

[Fluff]

The first two lines of code ie

Option Explicit
Private Dic As Object

must go at the very top of the userform module, before any code.

 

[LikeButtah1]

Fluff,
I really appreciate you walking me through this but now I'm getting run time error 91: Object variable or with block variable not set here :

Private Sub TextBox1_AfterUpdate()


Me.ListBox1.List = Dic(Me.TextBox1.Value).Value
End Sub

 

[Fluff]

Have you created a listbox? If so, is it called ListBox1?

 

[LikeButtah1]

Listbox1 is in the userform. Does it belong somewhere else?

 

[Fluff]

No it needs to be on the userform.
Have a look at this file & see if it works for you

 

[LikeButtah1]

Fluff & dmt32,

Thanks for everything. I tried dmt32's approach and it works for me. Really appreciate all the help.