Moving name from 1 listbox to another

[Jim Tibbetts]

Hello All - I have 2 ListBoxes on a UserForm. The 1st is populated with a
list of names based on a choice from a ComboBox. How can I "move" a name from
the 1st box (TeamListBox) to the 2nd box (DropsListBox - RowSource DROPS)
when it is clicked? The following will make the clicked name appear in the
2nd box, but how do I get the name to disappear from the 1st box ?

Private Sub TeamListBox_Click()
Worksheets("TeamData").Select
Range("INDEX(DROPS,1,1)").Select
Dim DropCounter As Integer
For DropCounter = 1 To 10
If ActiveCell.Value = "" Then
ActiveCell.Value = TeamListBox.Value
Exit Sub
Else
ActiveCell.Offset(1, 0).Select
End If
Next DropCounter
End Sub

Thanks for any ideas.

 

[Tom Ogilvy]

I would use additem to populate the listboxes rather than use the rowsource.
You will have a hard time removing an item from the list especially from the
click event. This shows how to do it when you use additem to populate and
use a commandbutton to move

Private Sub CommandButton1_Click()
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
ListBox2.AddItem ListBox1.List(i)
End If
Next
For i = ListBox1.ListCount - 1 To 0 Step -1
If ListBox1.Selected(i) Then
ListBox1.Selected(i) = False
ListBox1.RemoveItem i
End If
Next
End Sub

This also assumes the multiselect property of the listboxes has been set to
true.

 

[Jim Tibbetts]

Tom - As always, thanks for the quick reply. I modified your code slightly to
reflect the names of the listboxes. Here is what I have:

Private Sub TeamListBox_Click()
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
DropsListBox.AddItem TeamListBox.List(i) ***
End If
Next
For i = TeamListBox.ListCount - 1 To 0 Step -1
If TeamListBox.Selected(i) Then
TeamListBox.Selected(i) = False
TeamListBox.RemoveItem i
End If
Next
End Sub

I get an error "Permission denied" at the 4th line (marked ***). Any ideas?

Jim T

 

[Jim Cone]

Try...

DropsListBox.AddItem TeamListBox.List(i, 0)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Jim Tibbetts"
<[email protected]>
wrote in message
Tom - As always, thanks for the quick reply. I modified your code slightly to
reflect the names of the listboxes. Here is what I have:

Private Sub TeamListBox_Click()
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
DropsListBox.AddItem TeamListBox.List(i) ***
End If
Next
For i = TeamListBox.ListCount - 1 To 0 Step -1
If TeamListBox.Selected(i) Then
TeamListBox.Selected(i) = False
TeamListBox.RemoveItem i
End If
Next
End Sub

I get an error "Permission denied" at the 4th line (marked ***). Any ideas?

Jim T

 

[Jim Tibbetts]

Thanks Jim. I found out the problem was the DropsListBox was bound to a
RowSource. Once I removed that it worked. Now I can't get past this line:

Private Sub TeamListBox_Click()
For i = 0 To TeamListBox.ListCount - 1
If TeamListBox.Selected(i) Then
DropsListBox.AddItem TeamListBox.List(i)
End If
Next
For i = TeamListBox.ListCount - 1 To 0 Step -1
If TeamListBox.Selected(i) Then
TeamListBox.Selected(i) = False
TeamListBox.RemoveItem i ***
End If
Next
End Sub

Gives me an "Unspecified error" and won't remove the name.

 

[Jim Tibbetts]

Just realized there was the same problem with the TeamListBox. RowSource was
set to "TEAMLIST". "AddItem" doesn't work if ListBox is bound to data. I
cleared The RowSource and now I can't figure out how to populate the
TeamListBox.

 

[Tom Ogilvy]

Assume you have a named range TeamList with the values you want in the
listbox

Private Sub Userform_Initialize()
Dim cell as Range
Worksheets("TeamData").Select
for each cell in Range("TEAMLIST")
me.Teamlistbox.AddItem cell.Text
next
me.Teamlistbox.MultiSelect = true

End Sub


the intialize event is as written regardless of the name of your userform.
Put it in the userform module.

 

[Jim Tibbetts]

Beautiful Tom, thanks! That is just what I needed. I have another ListBox
that is populated with 125 names and I didn't want to type "AddItem" 125
times.

Thanks so much for your help.

 

[Tom Ogilvy]

here is another way if TeamList refers to a vertical oriented range of
cells.

example TeamList refers to TeamData!B5:B255

Private Sub Userform_Initialize()
Worksheets("TeamData").Select
me.Teamlistbox.List = Range("TeamList").Value
me.Teamlistbox.MultiSelect = true
End Sub

In Either method, the rowsource should not be set.

 

[Jim Tibbetts]

This last idea is the one that works. Thanks alot.

 

[Jim Tibbetts]

Another quick quwstion. What is the "me." before "TeamListBox"?

 

[Tom Ogilvy]

In the userform module, me refers to the userform.

 

[Jim Tibbetts]

I'm learning (thanks to you and others).

Thanks

 

[Jim Tibbetts]

Tom - Range TEAMLIST is 1 column, 10 rows long (A73:A82). Here is a snippet
of code you suggested I try to populate TeamListBox using TEAMLIST:

Dim cell as Range
Worksheets("TeamData").Select
for each cell in Range("TEAMLIST")
me.Teamlistbox.AddItem cell.Text
next

Will this code bring in all 10 cells in range TEAMLIST, or just the ones
that have a name in them? If it brings in all 10 cells even if they are
empty, how could this be modfied to populate TeamListBox with only cells that
contain names? Thank you for all of your help so far.

 

[Tom Ogilvy]

Dim cell as Range
Worksheets("TeamData").Select
for each cell in Range("TEAMLIST")
if len(trim(cell.Text)) <> 0 then
me.Teamlistbox.AddItem cell.Text
end if
next

 

[Jim Tibbetts]

That looks like what I need. I was close but not quite. Thank you so much for
all your help. You have been a lifesaver.

Thanks Tom