Use a filtered range as a Listbox RowSource

[Kichen]

Good morning everyone,

I have a worksheet that processes sales. If you click the Product Code cell in a new line, a userform pops up, containing a ListBox of all the products in the warehouse, then you double click one of them and it gets added to the invoice.

My problem comes because this ListBox will always show every single row in the warehouse sheet no matter what I do with its RowSource, and I want to only show products with a remaining quantity >0 and some other filter criteria added in there too.

This is the current code for my form:

Please Login or Register  to view this content.

This returns every single row, no matter if autofiltered or not. I've tried SpecialCells(xlCellTypeVisible) but I must have used it wrong because all I get is a blank listbox. The plan is to add ws1.AutoFilter.ApplyFilter before defining the RowSource to always get up-to-date data, would it work?

Thanks in advance!

[LeoTaxi]

Maybe this helps to get you on the right direction


Kind regards
Leo

[Kichen]

Hello Leo,

Thank you so much for taking the time to make this! However, it isn't exactly what I'm looking for.

I've tried to make a copy of my workbook stripping out all sensitive information to see if it helps make the problem clearer.

If you try clicking on any cell under "IDCaja" on the second sheet, you'll be presented with my current userform. There are two columns for "Total units" and "Sold units" plus a third one for "Remaining units" which is Total-Sold.

What I want to do is just make it so if Remaining Units = 0, that particular row doesn't appear (I made several articles have a remaining total of 0 for the example, from ID 86 to 99).

Or maybe your solution IS the answer I'm looking for, but I don't have the clarity of mind to understand the code very well, since I've been running without coffee all morning and the effects start to show -_- so I'll come back to this again in a couple hours and tell you if I managed to adapt it to my workbook!

Thanks again for your dedication!

Kichen

[LeoTaxi]

For Userform Initialize

Please Login or Register  to view this content.

Kind regards
Leo

[Kichen]

Got a type mismatch error at the beginning. Then, I fixed all my source data by checking that no cells returned any #N/A error (it's all formulas) adding IFERROR() to all of them, and now it works like a charm! Thank you so much Leo, you're a savior :D marking this as solved!