Using two multi select listboxes

[rutgerterhaar]

Hi!

I'm trying to make two multi select listboxes in excel. I have tried I myself, but I know the excistence of VBA only since one week .

In sheet1 I have a number i.e. 50 in "A1" . First I want to make a list of this number, so cell "A2" = 1, "A3" = 2, .... "A51" = 50. This range has to be variable since the number can be 1 but als 100.

Then I want to populate ListBox1 in sheet2 with the list from sheet1. Next I want a second ListBox (ListBox2) and two buttons ("Add item" and "remove items") to move items from ListBox1 to ListBox2 and back.

Finally i want the selection that is made in ListBox2 to be put in sheet1 in a specific column (let's say C) where "C1" is the first selected item from ListBox2 "C2" the second and so on. This range has to be variabel since the number of selected items is not known.

I hope this is a clear description.

Thanks for all the help!!!

Rutger.

 

[tlowry]

Here is an attempt

I've defined two additional buttons to:

1. Reset to starting position (CommandButton3)
2. Save selected in listbox2 to Worksheet (CommandButton4)

Note: sorting data was not requested nor implemented.

Private Sub CommandButton1_Click()
    ' move selected to listbox 2
    moveSelected ListBox1, ListBox2
End Sub
Private Sub CommandButton2_Click()
    ' move selected lb2 to lb1
    moveSelected ListBox2, ListBox1
End Sub
Private Sub CommandButton4_Click()
    'Save to Sheets("Sheet3") button
    Const sStart = "C"
    With Sheets("Sheet3")
        .Columns(3).ClearContents
        k = 0
        For i = 0 To ListBox2.ListCount - 1
            If ListBox2.Selected(i) = True Then
                .Range(sStart & "1").Offset(k, 0).Value = ListBox2.List(i)
                k = k + 1
            End If
        Next i
    End With
End Sub
Private Sub CommandButton3_Click()
    ' start button
    ListBox1.Clear
    ListBox2.Clear
    For k = 1 To Sheets("sheet1").Range("A1").Value
        ListBox1.AddItem Sheets("Sheet1").Range("A1").Offset(k, 0).Value
    Next k
End Sub
Sub moveSelected(lbSource, lbDest)
    If lbSource.ListIndex = -1 Then Exit Sub
    For i = 0 To lbSource.ListCount - 1
        If lbSource.Selected(i) = True Then
            lbDest.AddItem lbSource.List(i)
        End If
    Next i
    For i = lbSource.ListCount - 1 To 0 Step -1
        If lbSource.Selected(i) = True Then lbSource.RemoveItem (i)
     Next i
End Sub

 

[rutgerterhaar]

Hi!

Thanks for you're help!! The code is, after a little adjustment, perfect!

The lists are to be sorted, so I was looking in the properties of the ActiveX element but there is not an option for sorting. Does someone knows a way to sort the listboxes? It's probably not that hard, but I don't know .

Thanks!!

Rutger

 

[tlowry]

Sorting a listbox should be easy, but it isn't.

You should search this board for sorting and open another thread, if you don't find what you want.

 

[rutgerterhaar]

Oke! Thanks for your help!

Rutger