How to populate data in a listbox (USERFORM)


Active Member
Oct 29, 2005
Office Version
  1. 365
  1. Windows
i have a sheet that collects the name of dependants of our individual employees and their corresponding date of birth...example from a2:a3 employee 1001 then in b2:b3 is their dependants c2:c3 is their corresponding birth date. My query is, how can i populate all the dependants and their other data such as birthday names in a LISTBOX (i create a userform) while choosing an employee number on combobox...thanks for the help guys

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Something like this :-
Private Sub ComboBox1_Change()
    Dim DataSheet As Worksheet
    Dim FoundCell As Object
    Dim EmpNo As String
    Dim Rw As Long ' row number
    Dim MyItem As Long
    Set DataSheet = ThisWorkbook.Worksheets("data")
    '- find employee
    EmpNo = ComboBox1.Value
    Set FoundCell = DataSheet.Columns("A").Find(what:=EmpNo, after:=[a1])
    Rw = FoundCell.Row
    '- 2 column list box (set ColumnCount property)
    MyItem = 0
    While DataSheet.Cells(Rw, 1).Value = EmpNo
        ListBox1.List(MyItem, 0) = CStr(DataSheet.Cells(Rw, 2).Value)
        ListBox1.List(MyItem, 1) = CStr(DataSheet.Cells(Rw, 3).Value)
        MyItem = MyItem + 1
        Rw = Rw + 1
End Sub
Upvote 0
hi brian...thanks for the really helps..but i got one more question...why is that an error occurs whenever i type in the employee number in the combo box instead of choosing from the list..the error is : Runtime Error 19 Object variable or with block variable not set.....thanks
Upvote 0
The error is probably poping up because you have the code set to run off a ComboBox_Change event. This means it will fire off after each key is pressed. You could change this to an AfterUpdate event or if your employee numbers are all the same length (eg: 4 digits long) just run the code if Len(ComboBox1.text) > 3 so that it only fires on the 4th digit entered
Upvote 0
Hi fat cat...thanks....i tried what you have said but still the same error...actualy the error occurs when i enter the first number....
Upvote 0
If I understand your original post correctly, you want to select an employee from a ComboBox list and then have all the details of their dependents appear in a ListBox on a new UserForm.

Here's an example based on a worksheet laid out as follows and with a ListBox of employees on the worksheet.

And here's the code for a UserForm that contains just a single ListBox that gets filled with the dependents names & birthdays

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)    'this is the ListBox of employees on the worksheet
End Sub

Private Sub Worksheet_Activate()        'update the list of employees when you activate the sheet
LastRow = Range("A65536").End(xlUp).Row
For i = 2 To LastRow Step 2
    ListBox1.AddItem Cells(i, 1).Value
    ListBox1.ListIndex = 0
Next i
End Sub
Private Sub UserForm_Activate()

    vRow = (Sheets("Sheet1").ListBox1.ListIndex * 2) + 2    'work out the row number containing the selected employees data
    vCol = Range("IV" & vRow).End(xlToLeft).Column          'work out last column containing dependents
    ListBox1.ColumnCount = 2
    For i = 2 To vCol
        ListBox1.AddItem                                        'add another slot to the listbox
        ListBox1.Column(0, i - 2) = Cells(vRow, i).Value        'put the dependent's name in 1st column
        ListBox1.Column(1, i - 2) = Cells(vRow + 1, i).Value    'put their birthday in the 2nd column
    Next i
End Sub
Upvote 0
here's an example of my sheet...thanks for your time:
1Employee NumberDependants NameRelationshipAgeDate Of Birth
210001ShirleyWife51March 7, 1954
310001SusanDaughter13January 13, 1992
410001DavidSon12August 31, 1993
510001JaykeSon10November 8, 1995
610002GericoSon31February 23, 1974
710002ErwinSon1March 5, 2004
910008GarySon10May 4, 1995
1010008NiloSon7January 16, 1998
1110008EnricSon6May 12, 1999
1210008JerrySon5November 30, 2000
Upvote 0
can anyone help with this problem...

I have 2 combo boxes, one has the list of all employee number, and the second combo box should have a data of a particular employee number (see my table)
Upvote 0

Forum statistics

Latest member
al mugheen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back