Results 1 to 15 of 15

Userform cannot display info for two surnames that is the same from dropdownlist

Threaded View

  1. #1
    Forum Contributor
    Join Date
    09-03-2008
    Location
    Johannesburg, South Africa
    MS-Off Ver
    2021
    Posts
    351

    Userform cannot display info for two surnames that is the same from dropdownlist

    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
    Attached Files Attached Files
    Last edited by hendrikbez; 04-10-2024 at 12:37 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Can't assign numeric value to UserForm ComboBox with DropDownList Style
    By Greg M in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-14-2021, 06:15 AM
  2. To make a Dynamic Search DropDownList in UserForm.ComboBox
    By gnaske in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-08-2015, 05:56 PM
  3. [SOLVED] VBA Line to Send Userform Info to Parent Userform Textbox
    By excelforumkeys in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-01-2014, 04:28 PM
  4. display autoffiltered info from closed workbook in userform
    By Jarko28 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-14-2013, 12:28 PM
  5. [SOLVED] Open a userform from a userform, but preserve the info in the original userform
    By jfoerch in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-25-2013, 02:38 PM
  6. Replies: 1
    Last Post: 11-29-2012, 09:58 AM
  7. Textboxes within Userform to display info based on combobox selection
    By alter54 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-28-2012, 12:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1