Complicated listbox filtering code

Nitefox

New Member
Joined
Sep 23, 2013
Messages
22
I am looking to do some advanced filtering on a list box. My workbook is attached, and the userform im working on is called ActiveEquipment.

When the user selects CheckBox3 (titled Display active items only) on the userform, I would like it to filter out the records in the lbActiveItemList. Basically it should filter out items that are no longer in house (so only in house items remain). The lbActiveItemList list box generates its records from a click selection from one of two other list boxes above on the same userform called lbPOList and lbUnitList. CheckBox3 should perform differently depending which list box its displaying the information from (or which list box currently has the selected/highlighted record)

In the case of a selection being made from the lbPOList it should perform as follows:

For each individual record in the list box that is labelled as 'Receive' in column 3 (status) it should find the first record below (ignore above records) where the Column 5 data (barcode) both match, and while Column 3 is either 'Return' or 'Lost'.
Upon finding a match, it should then search for the records between these two matching Receive and Return or Lost records for another match in Column 5 (barcode) while Column 3 (status) is equal to 'Damaged' or 'Relocate', and filter them out of the list box. Then weather a Damaged or Relocate item was found or not the first two matching items (Receive and Return or Lost) should also be filtered out of the list box.
It should then start the cycle again and move onto the next Receive item in the list.
Finally all remaining records where Column 3 (status) equals 'Return', or 'Lost' should be removed.
Therefore only Receive, Relocate, or Damaged items that have not yet been Returned or Lost should remain.

In the case of a selection being made from the lbUnitList it should perform as follows (the differences are in bold):

For each individual record in the list box that is labelled as 'Receive' or 'Relocate' in column 3 (status), and while Column 6 (Unit - to) also matches the Column 1 of the selected record in lbUnitList, it should find the first record below (ignore above records) where the Column 5 data (barcode) both match, and while Column 3 is either 'Return' or 'Lost' or 'Relocate'. (in the case of Relocate, Column 8 (Unit - from) must match the Column 1 data of the selected record in the lbUnitList List Box)
Upon finding a match, it should then search for the records between these two matching records for another match in Column 5 (barcode) while Column 3 (status) is equal to 'Damaged' (not to 'Relocate' this time), and filter them out of the list box. Then weather a matching Damaged or Relocate item was found or not the first two matching items should also be filtered out of the list box.
It should then start the cycle again and move onto the next Receive or Relocate item in the list.
Finally all remaining records where Column 3 (status) equals 'Return', or 'Lost' should be removed.
Therefore only Receive, Relocate (in), or Damaged items that have not yet been Returned, Relocated (out) or Lost should remain.

List of lbActiveItemList Columns:
Column 1 = Record number
Column 2 = Date
Column 3 = Status
Column 4 = Item
Column 5 = Barcode
Column 6 = Unit (to)
Column 7 = PO#
Column 8 = Unit (from)

Thank you very much for you time.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

diddi

Well-known Member
Joined
May 20, 2004
Messages
2,936
Office Version
  1. 2010
Platform
  1. Windows
post your code and ask a specific question, you may have more luck obtaining help.
 

mohan.pandey87

Board Regular
Joined
Sep 14, 2012
Messages
146
It would be better if you can attach dummy file to make it more understandable and clear.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,156
Messages
5,600,033
Members
414,356
Latest member
death20

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top