VBA List Box Wizard???

tuccisal

New Member
Joined
Jan 26, 2005
Messages
4
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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

New Member
Joined
Jan 26, 2005
Messages
4
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

New Member
Joined
Jan 26, 2005
Messages
4

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 26, 2005
Messages
4
Thanks Norie! Your code worked perfectly. I added a quick sorting routine as well to reorder the selected items alphabetically. Thanks again. :biggrin:
 

Forum statistics

Threads
1,147,510
Messages
5,741,574
Members
423,668
Latest member
Audorin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top