How to populate data in a listbox (USERFORM)

fayez_MrExcel

Active Member
Joined
Oct 29, 2005
Messages
389
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
Something like this :-
Code:
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

Active Member
Joined
Oct 29, 2005
Messages
389
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

Active Member
Joined
Nov 5, 2004
Messages
336
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

Active Member
Joined
Oct 29, 2005
Messages
389

ADVERTISEMENT

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

Active Member
Joined
Nov 5, 2004
Messages
336
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
ABCD
1EmployeeDependant
21001MaryPeterDavid
310/06/196612/04/20018/07/2004
41002JuneHilarySam
512/12/19763/04/20025/05/2003
61003AprilSusanMike
710/06/196612/04/20018/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
Code:
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
Code:
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

Active Member
Joined
Oct 29, 2005
Messages
389
here's an example of my sheet...thanks for your time:
Book2
ABCDE
1Employee NumberDependants NameRelationshipAgeDate Of Birth
210001ShirleyWife51March 7, 1954
310001SusanDaughter13January 13, 1992
410001DavidSon12August 31, 1993
510001JaykeSon10November 8, 1995
610002GericoSon31February 23, 1974
710002ErwinSon1March 5, 2004
810008GraceWife301973
910008GarySon10May 4, 1995
1010008NiloSon7January 16, 1998
1110008EnricSon6May 12, 1999
1210008JerrySon5November 30, 2000
Sheet1
 

fayez_MrExcel

Active Member
Joined
Oct 29, 2005
Messages
389
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,223
Messages
5,570,975
Members
412,352
Latest member
Radek Z
Top