Listbox showing data searched in a sheet using the given combobox value

Hi everyone,

I'm facing a very hard (for me) issue. Ok this is my situation:

1. my worksheet has 7 sheets, let's say DE, E, DU, U, RI, Look, M

in sheet DE I have the following data:
COL
ROW A B C D E F
1 Name Invoice num Invoice date Value When due when paid
2 John 1 01.01.17 100 01.01.17 01.01.17
3 Bill 2 01.02.17 200 01.02.17 01.01.17
4 Howard 3 01.03.17 300 01.03.17
5 Mary 4 01.04.17 150 01.04.17
6 Tina 5 01.01.17 50 01.01.17 01.01.17
7 Bill 6 01.07.17 170 01.07.17
8 John 7 01.03.17 100 01.03.17 01.01.17
9 Mary 8 01.02.17 100 01.02.17

where the name in A are set from the sheet Look, which has:

COL
ROW A
1 Name
2 John
3 Bill
4 Mary
5 Howard

So column A in sheet "Look" has a order list of unique name
Sheet DE is where I register all the invoices. So I could have, in column A, the same more times.

My issue

I created an UserForm like the following:

Userform1
Combobox1
ListBox1

and I would like that ComboBox1 is populated by the list in column A of sheet "Look" (I menaged it, more or less) and then ListBox1 is populated with all the invoices which are not paid (so with column F empty) of the given data in ComboBox1 searched in the sheet "DE".
To better explain, if in ComboBox is set the value "John", the listbox will not show anything (all the invoices are paid).
If combobox shows "Mary", then listbox1 will shows ROW 5 and 9 of the sheet DE.
What shoudl be the code for that?
Thank you in advance.
Giacomo

I usually do that like this.

Set columns of ListBox1 to eight.

I store the Found record row in the last column, so If you want to change that record, you don't have to look it up again. Set the last Column width to 0 to hide this column.
E.g. .ColumnWidths = "50;50;50;50;50;50;50;0"

Private Sub ComboBox1_Change()
'Worksheets("dettaglio USCITE").Select
'ListBox1.Value = Application.VLookup(Me.ComboBox1.Value, Range("a2:a40"), 1, 0)
Dim WS As Worksheet
Dim C As Range
Dim LastRow As Long
Dim A As Long

Me.ListBox1.Clear

Set WS = Worksheets("DE")

With WS
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

    .Range ("a1:a" & LastRow)
    Set C = .Find(Me.ComboBox1, LookIn:=xlValues)
    If Not C Is Nothing Then
        firstAddress = C.Address
        Do
            If C.Offset(0, Range("V1").Column - 1) = "DUE" Then
                With Me.ListBox1
                    .AddItem C.Offset(0, 1)
                    .Column(1, .ListCount - 1) = C.Offset(0, 2)
                    .Column(2, .ListCount - 1) = C.Offset(0, 3)
                    .Column(3, .ListCount - 1) = C.Offset(0, 4)
                    .Column(4, .ListCount - 1) = C.Offset(0, 5)
                    .Column(5, .ListCount - 1) = C.Offset(0, 20)
                    .Column(6, .ListCount - 1) = C.Offset(0, 21)
                    'Store the row number of the found record.
                    'Use this value if you want to change the record later.
                    .Column(7, .ListCount - 1) = C.Row
                End With
            End If
            Set C = .FindNext(C)
        Loop While Not C Is Nothing And C.Address <> firstAddress
    End If
End With

End Sub

Make sure you change the name of the worksheets ("DE") to the correct name.

Make sure it's looking down the correct column ("A").

LastRow can't be 0. If you're getting 0, then it's likely not searching the correct column.

Ok, I think I've got it working like you want. At least to where you can view it.

I randomly changed some of the Due verbs, just so i could see if it was working.

You really should make a unique list from Column A on DE and NOT a list sheet. If someone is added, you'll have to remember to add them, whereas if you generate a unique list at runtime, the combobox will always have a current list of names.

Private Sub ComboBox1_Change()
'Worksheets("dettaglio USCITE").Select
'ListBox1.Value = Application.VLookup(Me.ComboBox1.Value, Range("a2:a40"), 1, 0)
Dim WS As Worksheet
Dim C As Range
Dim LastRow As Long
Dim A As Long

Me.ListBox1.Clear

Set WS = Worksheets("DE")

With WS
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

    With .Range("a1:a" & LastRow)
    Set C = .Find(Me.ComboBox1, LookIn:=xlValues)
    If Not C Is Nothing Then
        firstAddress = C.Address
        Do
            If C.Offset(0, Range("V1").Column - 1) = "Due" Then
                With Me.ListBox1
                    'Column B - Invoice Number
                    .AddItem C.Offset(0, 1)
                    'Invoice Dae
                    .Column(1, .ListCount - 1) = C.Offset(0, 2)
                    'Value
                    .Column(2, .ListCount - 1) = C.Offset(0, 3)
                    'When due
                    .Column(3, .ListCount - 1) = C.Offset(0, 4)
                    'When Paid
                    If C.Offset(0, 5) = "" Then
                        .Column(4, .ListCount - 1) = "No Date"
                    Else
                        .Column(4, .ListCount - 1) = C.Offset(0, 5)
                    End If
                    'Payment method
                    .Column(5, .ListCount - 1) = C.Offset(0, 20)
                    'Status
                    .Column(6, .ListCount - 1) = C.Offset(0, 21)
                    'row number of the record
                    .Column(7, .ListCount - 1) = C.Row
                End With
            End If
            Set C = .FindNext(C)
        Loop While Not C Is Nothing And C.Address <> firstAddress
    End If
    End With
End With

End Sub

P.S. I had to turn off a lot of the References because it keep erroring out on me.

In your 'message box', in the confirmation command button, add

worksheets("DU").range("F" & Me.ListBox1.Column(7, Me.ListBox1.ListIndex)) = MyDate

Column 7 is the stored record row value when we initially filled the listbox.