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"

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.