Last week, we saw how to move items from one listbox to another on an Excel worksheet.
Now I’ve added a page on the Contextures website, with similar instructions for listboxes on an Excel UserForm.
Thanks to Dave Peterson, who provided both sets of sample code.
ListBoxes in Excel UserForm
In the screen shot below, there is an Excel UserForm with two ListBoxes, and four command buttons, down the centre.
- The buttons with 2 arrows move ALL items from one list to the other, in the direction that the arrows are pointing
- The buttons with 1 arrow move the SELECTED items from one list to the other, in the direction that the arrow is pointing
Download the Move Listbox Items Sample File
To see the code, and test the ListBox move items code for a UserForm, you can download the ListBox UserForm Move Items sample workbook.
The file is in Excel 2007 format, and is zipped. It contains macros, so enable them if you want to test the code.
Related Excel VBA Tutorials
If you’re looking for a fun and exciting way to fill your holiday Monday, here are links to a few other UserForm and Excel VBA tutorials on my Contextures website:
Create an Excel UserForm Video
Excel VBA Edit Your Recorded Macro
______________________
Hi Debra,
Great tutorial(As always)
I was only wondering, wouldn’t it be possible to use just one loop(working backwards) that adds items to to listbox2 and remove items from litbox1
i did try and it works well or is there any downside to using it
[code]
For iCtr = Me.ListBox1.ListCount – 1 To 0 Step -1
If Me.ListBox1.Selected(iCtr) = True Then
Me.ListBox2.AddItem Me.ListBox1.List(iCtr)
Me.ListBox1.RemoveItem iCtr
End If
Next iCtr
[/code]
I wrote this code at least 3 years ago, so I’m not sure if I remember why I did it that way.
I’m not sure if I wrote it that way because of a problem or because it was a “teaching” moment — a manner that was more easily changed under different circumstances.
I’m betting that it was the latter. That the code was more easily modified if a requirement changed — like not adjusting the “sending” list or if it had to be copied elsewhere.
Who knows for sure (hehe).