Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving name from 1 listbox to another
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving name from 1 listbox to another
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving name from 1 listbox to another
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving name from 1 listbox to another
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving name from 1 listbox to another
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving name from 1 listbox to another
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving name from 1 listbox to another
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving name from 1 listbox to another
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. -- Jim T "Tom Ogilvy" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving name from 1 listbox to another
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. -- Regards, Tom Ogilvy "Jim Tibbetts" wrote in message ... 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. -- Jim T "Tom Ogilvy" wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving name from 1 listbox to another
This last idea is the one that works. Thanks alot.
-- Jim T "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Jim Tibbetts" wrote in message ... 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. -- Jim T "Tom Ogilvy" wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving name from 1 listbox to another
Another quick quwstion. What is the "me." before "TeamListBox"?
-- Jim T "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Jim Tibbetts" wrote in message ... 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. -- Jim T "Tom Ogilvy" wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving name from 1 listbox to another
In the userform module, me refers to the userform.
-- Regards, Tom Ogilvy "Jim Tibbetts" wrote in message ... Another quick quwstion. What is the "me." before "TeamListBox"? -- Jim T "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Jim Tibbetts" wrote in message ... 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. -- Jim T "Tom Ogilvy" wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving name from 1 listbox to another
I'm learning (thanks to you and others).
Thanks -- Jim T "Tom Ogilvy" wrote: In the userform module, me refers to the userform. -- Regards, Tom Ogilvy "Jim Tibbetts" wrote in message ... Another quick quwstion. What is the "me." before "TeamListBox"? -- Jim T "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "Jim Tibbetts" wrote in message ... 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. -- Jim T "Tom Ogilvy" wrote: 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving name from 1 listbox to another
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. -- Jim T "Tom Ogilvy" wrote: 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving name from 1 listbox to another
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 -- Regards, Tom Ogilvy "Jim Tibbetts" wrote in message ... 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. -- Jim T "Tom Ogilvy" wrote: 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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving name from 1 listbox to another
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 -- Jim T "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "Jim Tibbetts" wrote in message ... 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. -- Jim T "Tom Ogilvy" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving Mutli-Column Multiple-Selected Listbox items up or down | Excel Discussion (Misc queries) | |||
VBA: Creating listbox similar to the one in Pivot table (Listbox+Checkbox) | Excel Programming | |||
Moving cell values from sheet2 to sheet1 using UserForms and ListBox | Excel Programming | |||
Listbox--moving items up or down | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming |