Multi Columns, Multi Listbox Form

[bensdb]

Hi.

PLEASE HELP ME. I have scoured the internet all day trying to do this and cannot find what I want.

I have a form with a number of listboxes > 2. Say for arguaments sake- 5 listboxes.

I then have a range of data on a worksheet with 5 columns. There is an ID Key in the first column. In the 3rd column will be a number corresponding to the total number of listboxes. So, as we have 5 listboxes, some of the entries will be labeled 1, some 2 , some labeled 3 etc....

Basically when I open the form, Listbox 1 should have all the entries with the corresponding 1 in column 3 of the worksheet, Listbox 2 should have all the entries with 2 etc.

Underneath each listbox will be a button saying 'move here'. The user should be able to select multiple entries in each listbox, and from several listboxes at once, then click a button underneath one of the listboxes and those entries will move there.

No I have tried lots of different methods today and have just ended up coming to the point of wanting to cry, after trying and failing all day. My lack of knowledge of array's is really a stumbling block.

I have got to the point of creating a form with my 5 listboxes that auto populate with the correct multi column values, corresponding to the number in column 3 of the data range.

One solution That I thought would be to copy all the selected values from the listboxes into a temporary sheet, change all the values in the 3rd column to the corresponding listbox, and then do a lookup of the ID in the original range and replace the corresponding data with the new listbox value, then update all listboxes.

Any help...please!!!!

[bensdb]

An even sweeter way to do this would be to pull the data drom the initial range into a 2d array, then populate the listboxes depending on the number in the 3rd column. Then if it is selected and a button is clicked to move to listbox 2, for example, the number in column 3 of the array of the selected items would change to 2, and then some sort of refresh type code would update the listboxes.

[snb]

Why don't you post a sample workbook ?

[bensdb]

Ok after playing with loops and arrays for a while I have figured out how to write my range to an array, and then populate a listbox with data which contains a certain value in the third column of the array. No I have to figure out how to loop through the different unique values and add them to different listboxes, then the final stage in the project will be to figure out how to multi select across all the different listboxes and and then change theyre 3rd column value in the main array, then refresh and hopefully all the listboxes will update.


Sub FinalLooper()
Dim ws As Worksheet
Dim ar_main()
Dim x As Integer
Dim iCnt&

Set ws = Sheets("Register")
iCnt = 0
x = 0
i = 0
J = 0

Lr = ws.Range("A1").End(xlDown).Row 'Last row of the data set

ReDim ar_main(Lr - 2, 6)


'clear all listboxes & format them
Dim ctrl As Control
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "ListBox" Then
With ctrl
.Clear
.ColumnCount = 7
.ColumnWidths = "30;50;50;50;50;50;50"
End With
End If
Next



'Stores values in ar_main
For i = 0 To Lr - 2
For J = 0 To 6
ar_main(i, J) = ws.Cells(i + 2, J + 1)
Next
Next


Dim rnd As String
rnd = 1
'Counting instances of specified value in 3rd column of array
For i = 0 To UBound(ar_main, 1)
iCnt = iCnt - (ar_main(i, 3) = rnd)
Next i

Dim ar_1()
'Defines the new array's size based on count
ReDim ar_1(iCnt, 6)

'For each instance where there is a match in ar_main, add to new array
For i = 0 To Lr - 2
If (ar_main(i, 3) = rnd) Then
x = x + 1
For J = 0 To 6
ar_1(x - 1, J) = ar_main(i, J)
Next
End If
Next



UserForm1.ListBox1.List = ar_1
UserForm1.Show

End Sub

[snb]

I think in this case 3 lines of code will suffice;

Have a look over here: http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html