Populate Userform List box with table data on another sheet

Hi,

I hope someone could help with VBA code to populate a list box on a user form that gets the it's data from another sheet (DB) with all the data in a table. sheet attached

I need two things to happen with this user form:

1. When the All Staff button (Grey button) is pressed on the user form then the list box (Resultbox) must get populated with staff detail of the following columns: Name, Last Name, Branch, Cell Number for ALL the staff listed on sheet (DB).

2. When the Current Staff button (Blue button) is pressed on the user form then the list box (Resultbox) must get populated with staff detail of the following columns: Name, Last Name, Branch, Cell Number for only the staff that are currently employed and this is determined by column N which says if they are employed or not.

Thanks

Wow this is great!

If not too much to ask could you please help with code to use the search function when wanting to search employee data listed in columns: Name, Last Name, Branch on "DB" sheet but then to be displayed in the Resultbox as per array in previous code provided.

Originally Posted by nankw83

Private Sub cmdLookup_Click()

Dim a, b, Txt$
Txt = txtLookup.Value
a = Sheets("DB").Range("B2").CurrentRegion

ReDim b(1 To UBound(a), 1 To 4)
For X = 1 To UBound(a)
  If X = 2 Or InStr(1, a(X, 2), Txt, 1) > 0 Or InStr(1, a(X, 3), Txt, 1) > 0 Or InStr(1, a(X, 5), Txt, 1) > 0 Then
    i = i + 1
    b(i, 1) = a(X, 2): b(i, 2) = a(X, 3): b(i, 3) = a(X, 5): b(i, 4) = a(X, 9)
  End If
Next

ResultBox.List = b

End Sub

Thank you the search function works great works great!

Originally Posted by torachan

for the OP reduce your DB table to just the used area otherwise @sintek solution adds 2,800 lines of extra 'nothingness' to the listbox.

ignore above, i was referring to first posting.

Thanks yes that makes sense!

Rudidw,

The code should be like this, otherwise you will get so many balnk rows and unexpected result in certain condition.

Private Sub AllStaff_Click()
    Dim LR
    With Sheets("db").ListObjects(1).DataBodyRange
        LR = .Parent.Evaluate("max(if(" & .Columns(1).Address & "<>"""",row(" & .Columns(1).Address & ")))") - .Row + 1
        Me.ResultBox.List = Application.Index(.Value, Evaluate("row(1:" & LR & ")"), Array(2, 3, 5, 9))
    End With
End Sub

Private Sub CurrentStaff_Click()
    Dim x
    Me.ResultBox.Clear
    With Sheets("db").ListObjects(1).DataBodyRange
        x = Filter(.Parent.Evaluate("transpose(if(" & .Columns(13).Address & "=""yes"",row(1:" & .Rows.Count & ")))"), False, 0)
        If UBound(x) > -1 Then
            If UBound(x) = 0 Then
                Me.ResultBox.Column = Application.Index(.Value, Application.Transpose(x), [{2,3,5,9}])
            Else
                Me.ResultBox.List = Application.Index(.Value, Application.Transpose(x), [{2,3,5,9}])
            End If
        End If
    End With
End Sub

I would use Label (Label1 to Label9)

Private Sub AllStaff_Click()
    Dim LR
    With Sheets("db").ListObjects(1).DataBodyRange
        LR = .Parent.Evaluate("max(if(" & .Columns(1).Address & "<>"""",row(" & .Columns(1).Address & ")))") - .Row + 1
        Me.ResultBox.List = Application.Index(.Value, Evaluate("row(1:" & LR & ")"), Array(2, 3, 4, 5, 6, 7, 9, 10, 11))
    End With
    With Me.ResultBox
        .ColumnCount = 9
        .ColumnWidths = "100;100;0;100;0;0;100;0;0"
    End With
End Sub

Private Sub CurrentStaff_Click()
    Dim x
    Me.ResultBox.Clear
    With Sheets("db").ListObjects(1).DataBodyRange
        x = Filter(.Parent.Evaluate("transpose(if(" & .Columns(13).Address & "=""yes"",row(1:" & .Rows.Count & ")))"), False, 0)
        If UBound(x) > -1 Then
            If UBound(x) = 0 Then
                Me.ResultBox.Column = Application.Index(.Value, Application.Transpose(x), Array(2, 3, 4, 5, 6, 7, 9, 10, 11))
            Else
                Me.ResultBox.List = Application.Index(.Value, Application.Transpose(x), Array(2, 3, 4, 5, 6, 7, 9, 10, 11))
            End If
        End If
    End With
End Sub

Private Sub cmdLookup_Click()
    Dim LR, a, i As Long, ii As Long, iii As Long
    With Sheets("db").ListObjects(1).DataBodyRange
        LR = .Parent.Evaluate("max(if(" & .Columns(1).Address & "<>"""",row(" & .Columns(1).Address & ")))") - .Row + 1
        a = Application.Index(.Value, Evaluate("row(1:" & LR & ")"), Array(2, 3, 4, 5, 6, 7, 9, 10, 11))
    End With
    With Me.ResultBox
        .Clear
        For i = 1 To UBound(a, 1)
            For ii = 1 To UBound(a, 2)
                If UCase$(a(i, ii)) Like "*" & UCase$(Me.txtLookup) & "*" Then
                    .AddItem
                    For iii = 1 To UBound(a, 2)
                        .List(.ListCount - 1, iii - 1) = a(i, iii)
                    Next
                    Exit For
                End If
            Next
        Next
    End With
End Sub

Private Sub ResultBox_Click()
    Dim i As Long
    With Me.ResultBox
        For i = 1 To 9
            Me("label" & i).Caption = .List(.ListIndex, i - 1)
        Next
    End With
End Sub

Private Sub ResultBox_Click()
Dim i As Long
With Me.ResultBox
For i = 1 To 9
Me("label" & i).Caption = .List(.ListIndex, i - 1)
Next
End With
End Sub[/code][/QUOTE]

Sorry jindon but total noob here so I have no idea what Label you would be talking about

I have attached my workbook again with all your code.

I have added a text box called ListBox2 (bottom right box on user form) which I would like to automatically populate with different info when I select an employee in the Resultbox, but this info would be displayed in row format and not columns as before.

The info to be displayed in Listbox2 would be Name, Last Name, ID Number, Tax Number, Address, Email address and Start Date

Thanks for the help so far!

1) You only use part of my codes. If you are to use mine with other codes regarding this issue, I'm not going to do it, because they are all linked.

2) I don't understand why/how you want the other list box(ListBox2).

Originally Posted by jindon

1) You only use part of my codes. If you are to use mine with other codes regarding this issue, I'm not going to do it, because they are all linked.

2) I don't understand why/how you want the other list box(ListBox2).

Apologies I see I forgot to save the sheet with all your code in before uploading it here. I have uploaded it again.

The reason for the other box (ListBox2) is it would show specific details of the employee that was clicked in Resultbox.

So if you look at the user form, there are 3 boxes:
1. The big box (Resultbox) - display all staff or employed staff when those buttons are pressed and a search can also be done (this code you have provided and working great...thank you!)
2. Box bottom right (ListBox2) - will display specific info (Name, Last Name, ID Number, Tax Number, Address, Email address and Start Date) of the employee that is clicked in ResultBox.
3. Box above ListBox2 (Image1) - will display a photo of the employee that was clicked in ResultBox. this image will be in a folder on computer and named the same number as employee number in table on DB sheet.

So basically my goal with this userform is - you look for an employee in ResultBox, you click on that employee in ResultBox and then the details specific for that employee (see point 2. above) are displayed in ListBox2 and at the same time a photo of the employee will also be displayed (Image1)...hope this helps to understand this user form better.

Change to

Private Sub AllStaff_Click()
    Dim LR
    With Sheets("db").ListObjects(1).DataBodyRange
        LR = .Parent.Evaluate("max(if(" & .Columns(1).Address & "<>"""",row(" & .Columns(1).Address & ")))") - .Row + 1
        Me.ResultBox.List = Application.Index(.Value, Evaluate("row(1:" & LR & ")"), Array(2, 3, 4, 5, 6, 7, 9, 10, 11))
    End With
    With Me.ResultBox
        .ColumnCount = 9
        .ColumnWidths = "100;100;0;100;0;0;100;0;0"
    End With
End Sub

Private Sub CurrentStaff_Click()
    Dim x
    Me.ResultBox.Clear
    With Sheets("db").ListObjects(1).DataBodyRange
        x = Filter(.Parent.Evaluate("transpose(if(" & .Columns(13).Address & "=""yes"",row(1:" & .Rows.Count & ")))"), False, 0)
        If UBound(x) > -1 Then
            If UBound(x) = 0 Then
                Me.ResultBox.Column = Application.Index(.Value, Application.Transpose(x), Array(2, 3, 4, 5, 6, 7, 9, 10, 11))
            Else
                Me.ResultBox.List = Application.Index(.Value, Application.Transpose(x), Array(2, 3, 4, 5, 6, 7, 9, 10, 11))
            End If
        End If
    End With
End Sub

Private Sub cmdLookup_Click()
    Dim LR, a, i As Long, ii As Long, iii As Long
    With Sheets("db").ListObjects(1).DataBodyRange
        LR = .Parent.Evaluate("max(if(" & .Columns(1).Address & "<>"""",row(" & .Columns(1).Address & ")))") - .Row + 1
        a = Application.Index(.Value, Evaluate("row(1:" & LR & ")"), Array(2, 3, 4, 5, 6, 7, 9, 10, 11))
    End With
    With Me.ResultBox
        .Clear
        For i = 1 To UBound(a, 1)
            For ii = 1 To UBound(a, 2)
                If UCase$(a(i, ii)) Like "*" & UCase$(Me.txtLookup) & "*" Then
                    .AddItem
                    For iii = 1 To UBound(a, 2)
                        .List(.ListCount - 1, iii - 1) = a(i, iii)
                    Next
                    Exit For
                End If
            Next
        Next
    End With
End Sub

Private Sub ResultBox_Click()
    Dim i As Long, myCols
    Me.ListBox2.Clear
    myCols = Array(0, 1, 4, 5, 7, 8)
    Me.ListBox2.ColumnCount = UBound(myCols) + 1
    With Me.ResultBox
        Me.ListBox2.AddItem
        For i = 0 To UBound(myCols)
            Me.ListBox2.List(0, i) = .List(.ListIndex, myCols(i))
        Next
    End With
End Sub

Thanks jindon

ListBox2 should be listed in Row format and not Column format as your code works now as I mentioned in a previous post, therefore...

Name
Last Name
Id Number
Tax Number
Address
Email Address
Start Date

Thanks

Ahh.
Replace "ResultBox_Click" with

Private Sub ResultBox_Click()
    Dim i As Long, myCols
    Me.ListBox2.Clear
    myCols = Array(0, 1, 4, 5, 7, 8)
    Me.ListBox2.ColumnCount = UBound(myCols) + 1
    With Me.ResultBox
        For i = 0 To UBound(myCols)
            Me.ListBox2.AddItem .List(.ListIndex, myCols(i))
        Next
    End With
End Sub

Thank you! apologies for only replying now

I have tested it and all works well.

Thanks for the help from everyone that posted their solutions.