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.
--
Regards,
Tom Ogilvy
"Jim Tibbetts" wrote in message
...
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.
--
Jim T
"Jim Tibbetts" wrote:
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 T
"Jim Cone" wrote:
Try...
DropsListBox.AddItem TeamListBox.List(i, 0)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
"Jim Tibbetts"
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
"Tom Ogilvy" wrote:
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.
--
Regards,
Tom Ogilvy
"Jim Tibbetts" wrote in
message
...
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.
--
Jim T