Populate listbox in userform based on combobox selection

[Helmy00]

Hello Every one,

Much appreciated your support in advance, I'm new to vba coding and i hope i can find some help from expert like you guys.
i have combobox in userform " combobox5 " listed with users i want to populate list box with data from sheet " Data " based on selected user and if non selected show all users data.
as attached seeking support

 

[Saurabhj]

Hi Helmy00,

Welcome to MrExcel.

Please check Combo Box and List box file for reference.

Private Sub cmdSearch_Click()

Dim lastRow As Integer, rowno As Integer
    lastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
        lstDetails.Clear
    With Worksheets("Sheet1")
        lstDetails.AddItem
        lstDetails.List(0, 0) = .Range("A1")
        lstDetails.List(0, 1) = .Range("B1")
        lstDetails.List(0, 2) = .Range("C1")
        lstDetails.List(0, 3) = .Range("D1")
    End With
        

    If cboEmpName.Value = vbNullString Then
        'lstDetails.RowSource = "Sheet1!A2:D" & lastRow
        For rowno = 2 To lastRow
              lstDetails.AddItem
              lstDetails.List(rowno - 1, 0) = Worksheets("Sheet1").Range("A" & rowno)
              lstDetails.List(rowno - 1, 1) = Worksheets("Sheet1").Range("B" & rowno)
              lstDetails.List(rowno - 1, 2) = Worksheets("Sheet1").Range("C" & rowno)
              lstDetails.List(rowno - 1, 3) = Worksheets("Sheet1").Range("D" & rowno)
        Next
    Else
        For rowno = 2 To lastRow
            If cboEmpName.Value = Worksheets("Sheet1").Range("B" & rowno) Then
                lstDetails.AddItem
                lstDetails.List(1, 0) = Worksheets("Sheet1").Range("A" & rowno)
                lstDetails.List(1, 1) = Worksheets("Sheet1").Range("B" & rowno)
                lstDetails.List(1, 2) = Worksheets("Sheet1").Range("C" & rowno)
                lstDetails.List(1, 3) = Worksheets("Sheet1").Range("D" & rowno)
            End If
        Next
    End If
End Sub

Private Sub UserForm_Activate()
    Dim lastRow As Integer
    lastRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    cboEmpName.RowSource = "Sheet1!B2:B" & lastRow
End Sub

Thanks,
Saurabh

 

[Helmy00]

Thanks for your support Saurabh,
FYI
Sheet " Data " contains 5000 record for each column
each user has 180 records of data
30 users in column "B" unique list in "Members" Sheet
Combobox in userform listed with range of these 30 users as a droplist
i want to drop the list to pick any user then his 180 record of data populated into listbox
waiting your feedback
sorry for bothering you
i'll upload my file if you can help

try.xlsm

drive.google.com