How to populate data in a listbox (USERFORM)

[fayez_MrExcel]

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

 

[BrianB]

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)
    ListBox1.Clear
    MyItem = 0
    While DataSheet.Cells(Rw, 1).Value = EmpNo
        ListBox1.AddItem
        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
    Wend
End Sub

 

[fayez_MrExcel]

hi brian...thanks for the reply..it 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

 

[Fat Cat]

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

 

[fayez_MrExcel]

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....

 

[Fat Cat]

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.

Book1
	A	B	C	D
1	Employee	Dependant
2	1001	Mary	Peter	David
3		10/06/1966	12/04/2001	8/07/2004
4	1002	June	Hilary	Sam
5		12/12/1976	3/04/2002	5/05/2003
6	1003	April	Susan	Mike
7		10/06/1966	12/04/2001	8/07/2004
Sheet1

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

WORKSHEET

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

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

USERFORM

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

 

[fayez_MrExcel]

here's an example of my sheet...thanks for your time:

Book2
	A	B	C	D	E
1	Employee Number	Dependants Name	Relationship	Age	Date Of Birth
2	10001	Shirley	Wife	51	March 7, 1954
3	10001	Susan	Daughter	13	January 13, 1992
4	10001	David	Son	12	August 31, 1993
5	10001	Jayke	Son	10	November 8, 1995
6	10002	Gerico	Son	31	February 23, 1974
7	10002	Erwin	Son	1	March 5, 2004
8	10008	Grace	Wife	30	1973
9	10008	Gary	Son	10	May 4, 1995
10	10008	Nilo	Son	7	January 16, 1998
11	10008	Enric	Son	6	May 12, 1999
12	10008	Jerry	Son	5	November 30, 2000
Sheet1

 

[fayez_MrExcel]

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)