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.
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.
