VBA List Box Wizard???

[tuccisal]

Hello - I was wondering if anyone had any code to create the following:
I'm trying to build a very straightforward 'wizard' that consists of 2 list boxes. The list box on the left (multi-select) contains all of the choices a user can make. The list box on the right is empty and will receive the selections made in the list box on the right when the user presses a button to move the entries. Conversely, the user should also be able to move entires back from the right box to the left. Ultimately, the entries in the right box will be used to populate an array.

I've seen these sort of 'wizards' before so I know it is possible, but I'm stumped. Thanks.

Best regards,
Sal

 

[Norie]

Sal

This is definitely possible but could you supply some more information?

Where from or how are you populating the first listbox?

Is this on a userform or a worksheet?

 

[Norie]

The following code looks at column A and populates Listbox1 from it.

Then using CommandButton1 it transfers items from Listbox1 to Listbox2.

CommandButton2 transfers items from Listbox2 to Listbox1.

All the controls are on a userform.

Private Sub CommandButton1_Click()
Dim X As Long
Dim CountItems As Long
Dim MoveItem
    
    X = 0
    
    CountItems = ListBox1.ListCount
    
    While X < CountItems
    
        If ListBox1.Selected(X) Then
            MoveItem = ListBox1.List(X)
            ListBox1.RemoveItem (X)
            ListBox2.AddItem MoveItem
            CountItems = CountItems - 1
        Else
            X = X + 1
        End If
    Wend
End Sub

Private Sub CommandButton2_Click()
Dim Y As Long
Dim CountItems As Long
Dim MoveItem
    
    Y = 0
    
    CountItems = ListBox2.ListCount
    
    While Y < CountItems
    
        If ListBox2.Selected(Y) Then
            MoveItem = ListBox2.List(Y)
            ListBox2.RemoveItem (Y)
            ListBox1.AddItem MoveItem
            CountItems = CountItems - 1
        Else
            Y = Y + 1
        End If
    Wend

End Sub

Private Sub UserForm_Initialize()
Dim LastRow As Long
Dim I As Long

    LastRow = ActiveSheet.Range("A65536").End(xlUp).Row
    
    For I = 1 To LastRow
        ListBox1.AddItem ActiveSheet.Range("A" & I)
    Next I
    
End Sub

 

[tuccisal]

Norie - I've managed to find something that almost works correctly. Please visit :
http://www.dicks-blog.com/archives/2004/05/12/moving-data-between-listboxes/

This is exactly what I want but it doesn't seem to work when you multi-select items in the box to transfer..for example, if your box contains the 26 letters of the alphabet and you select a, b, and c...it will transfer c three times instead of transferring a, b, and c. Any thoughts?

As far as your questions, I plan on using a user form and will populate it via VBA (additem) upon opening the form.

 

[tuccisal]

Norie - I replied to your questions without realizing you already provided some code (didn't refresh my browser I guess). Anyway, I'll give your code a shot and let you know how I make out. Thanks in advance!

 

[Norie]

I don't know why the code in the link you posted does what you say.

I think the example given there is for a single select listbox.

 

[tuccisal]

Thanks Norie! Your code worked perfectly. I added a quick sorting routine as well to reorder the selected items alphabetically. Thanks again.