# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  > [SOLVED] VB for multi column listbox

## Nitefox

Im looking for some VB code for a userform Initialize for populate a multi column list box called lbPOlist with the following:

Column 1 (PO#):
List for all the unique entries in the sheet ReturnData in column I from row 6 on.
Column 2 (Date Created):
Find the first existing record on ReturnData sheet for each unique entry listed in list box column 1 and display the date from that record, which is in ReturnData sheet column D (so list box column 2 will show the date the unique entry in list box column 1 was first created)
Column 3 (Items In):
Display the total number of records for each unique entry listed in Column 1, where ReturnData column E for those records also equals 'Receive' (so it should take the data in the adjacent 1st column on the list box in the same row and display the number of how many records on ReturnData sheet where from I6 on matches it as well as the data in the E column for the same record being equal to 'Receive')
Column 4 (Items Out):
Same as above, however where column E is equal to 'Return'.
Column 5 (Items Relocated):
Same as above, however where column E is equal to 'Relocated'.
Column 6 (Items Lost):
Same as above, however where column E is equal to 'Lost'.
Column 7 (Items Damaged):
Same as above, however where column E is equal to 'Damaged'.
Column 8 (Active Items):
Active items are items that have been received, but not yet returned or lost. Therefor for each listbox column row it should take the number in column 3, and subtract both the numbers in the adjacent columns 4 and 6.

If it helps I have a dynamic range called POs for the ReturnData I column.
I have also attached my workbook. The userform is called ActiveEquipment.

Thank you so much for you time.

----------


## MickG

Try this:-
I'm not sure how accurate it is, but its a starting point !!!!



```
Please Login or Register  to view this content.
```

----------


## Nitefox

Thank you Mick this is awesome!
It does need a little tweaking though. There only seems to be 7 columns appearing, not 8. It looks like Column 7 (Items Damaged) is missing, and Column 8 (Active Items) is being displayed in the last column (which is currently the 7th column).
Also, is it possible to add a filter function, so that if the check box cbActivePOs is checked then all the records in the list box where the Active Items equals 0 are removed (only listing items where 1 or more is shown in that last column).

----------


## MickG

Try this:-
 Place this code in your "ActiveEquipment" Userform module.
The first code (Checkbox1) should alter the list depending on value in column 8.
 The Userform code should be improved to include column 8, Nb:= Comments ref:- Headers in code.
P.S. Note declaration of Array "Ray" at top of code




```
Please Login or Register  to view this content.
```


Regrds Mick

----------


## Nitefox

Thanks Mick, that Initialize code is working great now, but I'm getting a run time error 13 on the checkbox code (when the check box gets selected). It doesn't like the following line:
    ReDim nRay(1 To UBound(Ray, 1), 1 To 8)

I noticed the Dim Ray line is above the Private sub line, is that correct? I tried it both ways, but it still had the same error.
Also can I set all text alignment for every column to Center.

----------


## MickG

See Attached Ref:- "ReturnData" sheet

----------


## Nitefox

Thank you Mick, that works perfectly.
Would you mind helping me out further. I was hoping to use the same code for units (which is located in the ReturnData H column instead of the I column). I tried editing the code you already gave me but it didn't work out.
This data would display in a different list box on the same userform called lbUnitList. Below is a list for what should happen in each list box column (I have made all the differences from the previous PO list box to this one in bold).
I have also attached an updated workbook.

Column 1 (PO#):
List for all the unique entries in the sheet ReturnData in *column H* from row 6 on.
Column 2 (Date Created):
Find the first existing record on ReturnData sheet for each unique entry listed in list box column 1 and display the date from that record, which is in ReturnData sheet column D (so list box column 2 will show the date the unique entry in list box column 1 was first created)
Column 3 (Items In):
*Item in gets a little more complicated than it was for POs, since equipment can come into a unit in two ways. It can simply be Received in like before, but it can also be Relocated in (from another Unit). Therefore this total should be a sum of both the total number of records for each unique entry listed in Column 1 where ReturnData column E equals 'Receive', plus the total number of records where ReturnData column E equals 'Relocate' while the H column matches the unit in the adjacent Column 1 in the list box.*
Column 4 (Items Out):
*Similar to above, however it is the total sum of where column E is equal to 'Return' (while column H matches the adjacent Column 1 in list box), plus the records where column E is equal to 'Relocate' and column J matches the unit name in the adjacent column 1 in list box.*
Column 5 (Items Lost):
Same as normal, where column E is equal to 'Lost' while the adjacent column 1 is equal to *column H*.
Column 6 (Items Damaged):
Same as normal, however where column E is equal to 'Damaged' while the adjacent column 1 is equal to *column H*.
Column 7 (Active Items):
Active items are items that have been received and *relocated in*, but not yet returned, lost, or *relocated out*. Therefor for each listbox column row it should take the number in column 3, and subtract both the numbers in the adjacent columns 4 and *5.*

N.B. There are only 7 columns on this one. There is no individual Relocate total column, since that information has been absorbed into Items in and Items out (columns 3 and 4).
My eventual plan is so that when the user selects a item from either of the two top list boxes (lbPOList or lbUnitList) in this userform, it displays all the records specific to that unique Unit or PO (column 1) in the below list box.
Thank you for all your help.

----------


## MickG

New code added to existing code as below:-



```
Please Login or Register  to view this content.
```


Regards Mick

----------


## Nitefox

Thank you very Mick. I just spotted a small error with the second 'Unit' List in Column 4 (Out). When checking for Relocate in the E column, it should only find records where the matching unit value in the adjacent 1st column in the list is present in the *J column* on the sheet. It is currently matching it with the H column. That was correct for Relocating items in, but the unit its getting Relocated out of is listed in J column.

Also how can I change this check box code to do the same thing for the unit list box (I updated the check box and list box names in the code):




```
Please Login or Register  to view this content.
```


Many thanks,
James

----------


## MickG

Ref second code to fill listbox.
At the moment the code to fill column 4 of the list box,loops through column H" and compares its value with The Unique values for column "H", returning matching ("Receive", "Return") found in column "E"
Do you want it to now Compare the Unique values with Column "J" only , or with column "J" and column "H". Then returning matching ("Receive", "Return") found in column "E".

Below is the modified code:-
 I note the checkboxes are now called "Checkbox1" and "Checkbox2"

----------


## Nitefox

Column Three (Items In) should actually be Receive and Relocate. (I made that change on my end, it was a easy on to change)
When an item is relocated the unit its relocated to goes in H and the unit its being relocated out from is J.
Therefore when a Relocated Item is being matched for the Incoming column 3 it should be checking for the match in the H column only (which it does). When a Relocated Item is being matched for the Outgoing column 4 it should be checking for the match in the *J column only*.
Regards,
James

----------


## Nitefox

Sorry Mick, I just spotted another issue. CheckBox2 (Display active units only), works fine by itself, however as soon as I select CheckBox1 (Display active POs only) then the next time CheckBox2 is selected or deselected it no longer functions correctly.
I have attached the workbook.
Thanks,
James

----------


## MickG

I have  changed to array Variable "nRay" to "nRay1" in checkbox1 code, and have modified the second listbox code- See code remarks
Regrds Mick

----------


## Nitefox

Changing the offset to -5 for column 4 fixes the issue for relocated items, however it screws up Return items. Return items should still be looking at the -3 offset (like before).
Also I think the line to calculate column 7 should be after the column 4 code (otherwise it seems to not be including the number from column 4 into the calculation).
The CheckBox works great now, thanks.
Regards James

----------

