I using this code to get data from dropdown list
But if I have two surnames that is the same, I cannot get it to show the correct data from each same surname, it show the first name of the first person of the first same surname.
So if say if the first same surname is Pienaar with two names (Piet and Sannie) and second same surname with two names (Jaapie and Sarie, so when I choose any same surname it show 3 names showing (Piet, Sannie and Jaapie).
only happening with same surname. It must show only the name or names of each same surname.
Private Sub cboSurname_Change()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim surname As String
Dim surnameRows() As Long
Dim surnameRowIndex As Long
Dim memberIndex As Long
Set ws = ThisWorkbook.Sheets("Register")
lastRow = ws.Range("D" & Rows.Count).End(xlUp).Row
surname = Me.cboSurname.Value
'Clear the existing data in the controls
ClearControlValues
'Find all the rows with the selected surname
ReDim surnameRows(0)
surnameRowIndex = 0
For i = 10 To lastRow
If ws.Range("C" & i).Value = surname Then
surnameRowIndex = surnameRowIndex + 1
ReDim Preserve surnameRows(surnameRowIndex)
surnameRows(surnameRowIndex) = i
End If
Next i
'Populate the controls with the information for all the rows with the selected surname
memberIndex = 1
For surnameRowIndex = 1 To UBound(surnameRows)
Me.Controls("txtNaam" & memberIndex).Value = ws.Range("D" & surnameRows(surnameRowIndex)).Value
Me.Controls("txtverjaar" & memberIndex).Value = ws.Range("E" & surnameRows(surnameRowIndex)).Value
Me.Controls("txtselfoon" & memberIndex).Value = ws.Range("I" & surnameRows(surnameRowIndex)).Value
memberIndex = memberIndex + 1
If memberIndex > 5 Then
Exit For
End If
Next surnameRowIndex
End Sub
Private Sub ClearControlValues()
Dim i As Long
For i = 1 To 5
Me.Controls("txtNaam" & i).Value = ""
Me.Controls("txtverjaar" & i).Value = ""
Me.Controls("txtselfoon" & i).Value = ""
Next i
Me.txtHuis.Value = ""
Me.txtWerk.Value = ""
Me.txtadres.Value = ""
End Sub
Private Sub PopulateSurnameDropdown()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Set ws = ThisWorkbook.Sheets("Register")
lastRow = ws.Range("C" & Rows.Count).End(xlUp).Row
'Clear the dropdown list
Me.cboSurname.Clear
'Populate the dropdown list with unique surnames
For i = 10 To lastRow
If Not IsEmpty(ws.Range("C" & i).Value) Then
Me.cboSurname.AddItem ws.Range("C" & i).Value
End If
Next i
End Sub
Bookmarks