Pick Several, Add All, Remove All

[Leo Elbertse]

I've been searching quite a number of excel sites but haven't found
what I'm looking for:

The standard dialogbox (similar to the box you get when you click on
the 'To' button in Outlook|New Message). The box has 2 picking lists:

- left - not chosen yet
- right - chosen items

Clicking on an item in either list will transfer the item to the other
list.

In addition there are the 'Add All' and 'Remove All' buttons, that
respectively add all items from the left list to the right, and remove
all all items from the right list to reinstate them in the left.

I suppose there are numerous ways to manage this and am looking at:

- An adaptation of J Walkenbach's GetOption
- The, for me brandnew, Dictionary-object

Should anyone have a pertinent idea how to tackle this issue, or for
that matter know of a existing template, I'm all ear.

On the other hand, should this really be something new and should you
want to have a copy of the final VBA, just drop me a line.

Leo

[Rob van Gelder]

I would maintain two listboxes (actually I use the listview controls in
report view - they are sexier)
The buttons would perform the moving between. Or if you want to get really
smart you could play with the dragdrop facilities.

"Leo Elbertse" <LeoEl...@enper.nl> wrote in message
news:9js0vv413phka9qls...@4ax.com...

[Dave Peterson]

Is this on a userform?

If yes, then this might work ok for you.

I created a userform with two listboxes (listbox1 and listbox2)
and four commandbuttons:

BTN_moveAllLeft
BTN_moveAllRight
BTN_MoveSelectedLeft
BTN_MoveSelectedRight

And behind the userform, I had this code:

Option Explicit
Private Sub BTN_moveAllLeft_Click()

Dim iCtr As Long

For iCtr = 0 To Me.ListBox2.ListCount - 1
Me.ListBox1.AddItem Me.ListBox2.List(iCtr)
Next iCtr

Me.ListBox2.Clear
End Sub
Private Sub BTN_moveAllRight_Click()

Dim iCtr As Long

For iCtr = 0 To Me.ListBox1.ListCount - 1
Me.ListBox2.AddItem Me.ListBox1.List(iCtr)
Next iCtr

Me.ListBox1.Clear
End Sub
Private Sub BTN_MoveSelectedLeft_Click()

Dim iCtr As Long

For iCtr = 0 To Me.ListBox2.ListCount - 1
If Me.ListBox2.Selected(iCtr) = True Then
Me.ListBox1.AddItem Me.ListBox2.List(iCtr)
End If
Next iCtr

For iCtr = Me.ListBox2.ListCount - 1 To 0 Step -1
If Me.ListBox2.Selected(iCtr) = True Then
Me.ListBox2.RemoveItem iCtr
End If
Next iCtr

End Sub
Private Sub BTN_MoveSelectedRight_Click()

Dim iCtr As Long

For iCtr = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(iCtr) = True Then
Me.ListBox2.AddItemme.ListBox1.List (iCtr)
End If
Next iCtr

For iCtr = Me.ListBox1.ListCount - 1 To 0 Step -1
If Me.ListBox1.Selected(iCtr) = True Then
Me.ListBox1.RemoveItem iCtr
End If
Next iCtr

End Sub
Private Sub UserForm_Initialize()

Dim iCtr As Long

With Me.ListBox1
For iCtr = 1 To 10
.AddItem "This is a test" & iCtr
Next iCtr
End With

With Me.ListBox2
For iCtr = 1 To 10
.AddItem "This is a not a test" & iCtr
Next iCtr
End With

Me.ListBox1.MultiSelect = fmMultiSelectMulti
Me.ListBox2.MultiSelect = fmMultiSelectMulti

End Sub

--

Dave Peterson
ec3...@msn.com

[Dave Peterson]

And change this line:
Me.ListBox2.AddItemme.ListBox1.List (iCtr)
to
Me.ListBox2.AddItem Me.ListBox1.List(iCtr)

(One mass change before sending was a mistake!)

--

Dave Peterson
ec3...@msn.com

[Leo Elbertse]

Dave and Rob,

Thanks very much!

I've copied Dave's code lock, stock and barrel. All I have to do now
is populate the listboxes correctly.

Again thanks,

Leo

On Mon, 29 Dec 2003 18:30:51 -0600, Dave Peterson <ec3...@msn.com>
wrote: