| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't work twice
OK, I give up. Can someone please tell me why the following macro works
perfectly the first time the TeamAddsListBox is clicked, but the second time it is clicked, the TEAMLIST range and the TeamListBox are not updated from the TeamAddsListBox. Private Sub TeamAddsListBox_Click() For a = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(a) Then TeamListBox.AddItem TeamAddsListBox.List(a) End If Next For a = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(a) Then TeamAddsListBox.Selected(a) = False TeamAddsListBox.RemoveItem a End If Next Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value End Sub If I comment out the last line, the TEAMLIST is updated and sorted correctly and the name is added to the TeamListBox, but it is just added onto the end of the list. I need to have the list in the TeamListBox mirror the list in TEAMLIST. The name is removed from the TeamAddsList (as it should be) in either case. I have been stuggling with this all morning. Any help will be greatly appreciated. Thanks, -- Jim T |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro doesn't work twice
try setting your variables to nothing @ the end of the sub.......
a=nothing just an idea that might help. susan On Feb 6, 1:32 pm, Jim Tibbetts wrote: OK, I give up. Can someone please tell me why the following macro works perfectly the first time the TeamAddsListBox is clicked, but the second time it is clicked, the TEAMLIST range and the TeamListBox are not updated from the TeamAddsListBox. Private Sub TeamAddsListBox_Click() For a = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(a) Then TeamListBox.AddItem TeamAddsListBox.List(a) End If Next For a = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(a) Then TeamAddsListBox.Selected(a) = False TeamAddsListBox.RemoveItem a End If Next Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value End Sub If I comment out the last line, the TEAMLIST is updated and sorted correctly and the name is added to the TeamListBox, but it is just added onto the end of the list. I need to have the list in the TeamListBox mirror the list in TEAMLIST. The name is removed from the TeamAddsList (as it should be) in either case. I have been stuggling with this all morning. Any help will be greatly appreciated. Thanks, -- Jim T |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro doesn't work twice
Thanks for the reply Susan. I added "a=nothing" (without the quotes) after
the last line but got an error message. -- Jim T "Susan" wrote: try setting your variables to nothing @ the end of the sub....... a=nothing just an idea that might help. susan On Feb 6, 1:32 pm, Jim Tibbetts wrote: OK, I give up. Can someone please tell me why the following macro works perfectly the first time the TeamAddsListBox is clicked, but the second time it is clicked, the TEAMLIST range and the TeamListBox are not updated from the TeamAddsListBox. Private Sub TeamAddsListBox_Click() For a = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(a) Then TeamListBox.AddItem TeamAddsListBox.List(a) End If Next For a = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(a) Then TeamAddsListBox.Selected(a) = False TeamAddsListBox.RemoveItem a End If Next Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value End Sub If I comment out the last line, the TEAMLIST is updated and sorted correctly and the name is added to the TeamListBox, but it is just added onto the end of the list. I need to have the list in the TeamListBox mirror the list in TEAMLIST. The name is removed from the TeamAddsList (as it should be) in either case. I have been stuggling with this all morning. Any help will be greatly appreciated. Thanks, -- Jim T |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro doesn't work twice
Jim,
I tried to recreate this and failed miserably. I assume TEAMLIST is a named range in the workbook? How is TeamAddsListBox populated, RowSource or AddItem, and from where? Is TeamAddsListBox a multiselect listbox or single select? I see the point of loop 1 and loope 2, move the selected item form one listbox to another and delete from the former (although it is overkill if it is a single seelct), but I am lost as to what this code aims to do Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value When I ran this bit, TeamListBox filled with many repeated instances on one value. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Tibbetts" wrote in message ... Thanks for the reply Susan. I added "a=nothing" (without the quotes) after the last line but got an error message. -- Jim T "Susan" wrote: try setting your variables to nothing @ the end of the sub....... a=nothing just an idea that might help. susan On Feb 6, 1:32 pm, Jim Tibbetts wrote: OK, I give up. Can someone please tell me why the following macro works perfectly the first time the TeamAddsListBox is clicked, but the second time it is clicked, the TEAMLIST range and the TeamListBox are not updated from the TeamAddsListBox. Private Sub TeamAddsListBox_Click() For a = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(a) Then TeamListBox.AddItem TeamAddsListBox.List(a) End If Next For a = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(a) Then TeamAddsListBox.Selected(a) = False TeamAddsListBox.RemoveItem a End If Next Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value End Sub If I comment out the last line, the TEAMLIST is updated and sorted correctly and the name is added to the TeamListBox, but it is just added onto the end of the list. I need to have the list in the TeamListBox mirror the list in TEAMLIST. The name is removed from the TeamAddsList (as it should be) in either case. I have been stuggling with this all morning. Any help will be greatly appreciated. Thanks, -- Jim T |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro doesn't work twice
Hoo Boy, this will take some explaining. Yes, TEAMLIST is a named range. All
of my named ranges are in CAPS. TeamAddsListBox is populated by the value of a named range ADDLIST which is populated by the value of a named cell that changes based on a selection from a ComboBox. All ListBoxes are SingleSelect and a procedure is triggered by the Click event. The other code is there because I am also populating a range called DROPS in the spreadsheet. I need this as a temp holding area until the DROP button is clicked. The other code was copied from another porcedure and is used to re-sort the list after changes are made. I could overwhelm you with more info if you need it. Thanks for trying to help me out. -- Jim T "Bob Phillips" wrote: Jim, I tried to recreate this and failed miserably. I assume TEAMLIST is a named range in the workbook? How is TeamAddsListBox populated, RowSource or AddItem, and from where? Is TeamAddsListBox a multiselect listbox or single select? I see the point of loop 1 and loope 2, move the selected item form one listbox to another and delete from the former (although it is overkill if it is a single seelct), but I am lost as to what this code aims to do Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value When I ran this bit, TeamListBox filled with many repeated instances on one value. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Tibbetts" wrote in message ... Thanks for the reply Susan. I added "a=nothing" (without the quotes) after the last line but got an error message. -- Jim T "Susan" wrote: try setting your variables to nothing @ the end of the sub....... a=nothing just an idea that might help. susan On Feb 6, 1:32 pm, Jim Tibbetts wrote: OK, I give up. Can someone please tell me why the following macro works perfectly the first time the TeamAddsListBox is clicked, but the second time it is clicked, the TEAMLIST range and the TeamListBox are not updated from the TeamAddsListBox. Private Sub TeamAddsListBox_Click() For a = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(a) Then TeamListBox.AddItem TeamAddsListBox.List(a) End If Next For a = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(a) Then TeamAddsListBox.Selected(a) = False TeamAddsListBox.RemoveItem a End If Next Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value End Sub If I comment out the last line, the TEAMLIST is updated and sorted correctly and the name is added to the TeamListBox, but it is just added onto the end of the list. I need to have the list in the TeamListBox mirror the list in TEAMLIST. The name is removed from the TeamAddsList (as it should be) in either case. I have been stuggling with this all morning. Any help will be greatly appreciated. Thanks, -- Jim T |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro doesn't work twice
Bob - Let's forget my last reply. I just re-read it and I don't think it is
very clear. From the beginning: I have a UserForm with a ComboBox, 4 ListBoxes; TeamListBox1, TeamListBox2, DropsListBox and TeamAddsListBox as well as 3 CommandButtons; Drop, OK and Cancel. When the UserForm initiates only the ComboBox, TeamListBox1, DropsListBox, DropButton and CancelButton are visible. When a team name is clicked in the ComboBox it populates a named range with the name of the team. I use the team name to populate TeamListBox1 as well as named range TEAMLIST (I type all my named ranges in CAPS). I have 3 named ranges that I use as temporary holding areas until a button is clicked. DROPS, TEAMLIST and ADDLIST. When a golfer's name is clicked in TeamListBox1 it triggers the TeamListBox1_Clicked procedure Which moves the name from TeamListBox1 to DropsListBox and updates DROPS and TEAMLIST. If a mistake is made, clicking on the name in DropsListBox sends it back to TeamListBox1 and again updates DROPS and TEAMLIST. When the DropButton is clicked it runs a procedure that places the names in the DROPS temp area elsewhere in the spreadsheet and hides TeamListBox1, DropsListBox and the DropButton. It also reveals TeamListBox2, TeamAddsListBox and the OKButton. That part of my project works well. The problem starts when I try to do the same basic thing of moving a name from TeamAddsListBox to TeamListBox2. I copied the code that works and changed the references, but when a name in TeamAddsListBox is clicked, it doesn't get added to the list in TeamListBox2. I can't figure out what is different. I hope I am not making your eyes glaze over with all of this. I really do appreciate any wisdom you can impart to this poor soul. Jim T "Bob Phillips" wrote: Jim, I tried to recreate this and failed miserably. I assume TEAMLIST is a named range in the workbook? How is TeamAddsListBox populated, RowSource or AddItem, and from where? Is TeamAddsListBox a multiselect listbox or single select? I see the point of loop 1 and loope 2, move the selected item form one listbox to another and delete from the former (although it is overkill if it is a single seelct), but I am lost as to what this code aims to do Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value When I ran this bit, TeamListBox filled with many repeated instances on one value. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Tibbetts" wrote in message ... Thanks for the reply Susan. I added "a=nothing" (without the quotes) after the last line but got an error message. -- Jim T "Susan" wrote: try setting your variables to nothing @ the end of the sub....... a=nothing just an idea that might help. susan On Feb 6, 1:32 pm, Jim Tibbetts wrote: OK, I give up. Can someone please tell me why the following macro works perfectly the first time the TeamAddsListBox is clicked, but the second time it is clicked, the TEAMLIST range and the TeamListBox are not updated from the TeamAddsListBox. Private Sub TeamAddsListBox_Click() For a = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(a) Then TeamListBox.AddItem TeamAddsListBox.List(a) End If Next For a = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(a) Then TeamAddsListBox.Selected(a) = False TeamAddsListBox.RemoveItem a End If Next Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value End Sub If I comment out the last line, the TEAMLIST is updated and sorted correctly and the name is added to the TeamListBox, but it is just added onto the end of the list. I need to have the list in the TeamListBox mirror the list in TEAMLIST. The name is removed from the TeamAddsList (as it should be) in either case. I have been stuggling with this all morning. Any help will be greatly appreciated. Thanks, -- Jim T |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro doesn't work twice
Opps - I forgot the code that doesn't work. Here it is.
Private Sub TeamAddsListBox_Click() For A = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(A) Then TeamListBox2.AddItem TeamAddsListBox.List(A) End If Next For A = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(A) Then TeamAddsListBox.Selected(A) = False TeamAddsListBox.RemoveItem A End If Next There is more, but the problem is TeamListBox2 isn't being updated. TeamAddsList is being updated properly, though. -- Jim T "Bob Phillips" wrote: Jim, I tried to recreate this and failed miserably. I assume TEAMLIST is a named range in the workbook? How is TeamAddsListBox populated, RowSource or AddItem, and from where? Is TeamAddsListBox a multiselect listbox or single select? I see the point of loop 1 and loope 2, move the selected item form one listbox to another and delete from the former (although it is overkill if it is a single seelct), but I am lost as to what this code aims to do Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value When I ran this bit, TeamListBox filled with many repeated instances on one value. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Tibbetts" wrote in message ... Thanks for the reply Susan. I added "a=nothing" (without the quotes) after the last line but got an error message. -- Jim T "Susan" wrote: try setting your variables to nothing @ the end of the sub....... a=nothing just an idea that might help. susan On Feb 6, 1:32 pm, Jim Tibbetts wrote: OK, I give up. Can someone please tell me why the following macro works perfectly the first time the TeamAddsListBox is clicked, but the second time it is clicked, the TEAMLIST range and the TeamListBox are not updated from the TeamAddsListBox. Private Sub TeamAddsListBox_Click() For a = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(a) Then TeamListBox.AddItem TeamAddsListBox.List(a) End If Next For a = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(a) Then TeamAddsListBox.Selected(a) = False TeamAddsListBox.RemoveItem a End If Next Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value End Sub If I comment out the last line, the TEAMLIST is updated and sorted correctly and the name is added to the TeamListBox, but it is just added onto the end of the list. I need to have the list in the TeamListBox mirror the list in TEAMLIST. The name is removed from the TeamAddsList (as it should be) in either case. I have been stuggling with this all morning. Any help will be greatly appreciated. Thanks, -- Jim T |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro doesn't work twice
Jin,
Assuing I get (no guarantee there though <G), I wonder if the problem is caused by re-entering the event. This seems to do what you want Private fReEntry As Boolean Private Sub TeamAddsListBox_Click() If Not fReEntry Then fReEntry = True TeamListBox.AddItem TeamAddsListBox.Value TeamAddsListBox.RemoveItem TeamAddsListBox.ListIndex Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Replace What:="#N/A", Replacement:="" Range("TEAMLIST").Sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select fReEntry = False End If End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Tibbetts" wrote in message ... Hoo Boy, this will take some explaining. Yes, TEAMLIST is a named range. All of my named ranges are in CAPS. TeamAddsListBox is populated by the value of a named range ADDLIST which is populated by the value of a named cell that changes based on a selection from a ComboBox. All ListBoxes are SingleSelect and a procedure is triggered by the Click event. The other code is there because I am also populating a range called DROPS in the spreadsheet. I need this as a temp holding area until the DROP button is clicked. The other code was copied from another porcedure and is used to re-sort the list after changes are made. I could overwhelm you with more info if you need it. Thanks for trying to help me out. -- Jim T "Bob Phillips" wrote: Jim, I tried to recreate this and failed miserably. I assume TEAMLIST is a named range in the workbook? How is TeamAddsListBox populated, RowSource or AddItem, and from where? Is TeamAddsListBox a multiselect listbox or single select? I see the point of loop 1 and loope 2, move the selected item form one listbox to another and delete from the former (although it is overkill if it is a single seelct), but I am lost as to what this code aims to do Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value When I ran this bit, TeamListBox filled with many repeated instances on one value. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Tibbetts" wrote in message ... Thanks for the reply Susan. I added "a=nothing" (without the quotes) after the last line but got an error message. -- Jim T "Susan" wrote: try setting your variables to nothing @ the end of the sub....... a=nothing just an idea that might help. susan On Feb 6, 1:32 pm, Jim Tibbetts wrote: OK, I give up. Can someone please tell me why the following macro works perfectly the first time the TeamAddsListBox is clicked, but the second time it is clicked, the TEAMLIST range and the TeamListBox are not updated from the TeamAddsListBox. Private Sub TeamAddsListBox_Click() For a = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(a) Then TeamListBox.AddItem TeamAddsListBox.List(a) End If Next For a = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(a) Then TeamAddsListBox.Selected(a) = False TeamAddsListBox.RemoveItem a End If Next Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value End Sub If I comment out the last line, the TEAMLIST is updated and sorted correctly and the name is added to the TeamListBox, but it is just added onto the end of the list. I need to have the list in the TeamListBox mirror the list in TEAMLIST. The name is removed from the TeamAddsList (as it should be) in either case. I have been stuggling with this all morning. Any help will be greatly appreciated. Thanks, -- Jim T |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro doesn't work twice
Thanks Bob. It didn't like
Range("TEAMLIST").Value = TeamListBox.List Unspecified error. However, I have discovered the problem, but I can't fix it. See my latest post. -- Jim T "Bob Phillips" wrote: Jin, Assuing I get (no guarantee there though <G), I wonder if the problem is caused by re-entering the event. This seems to do what you want Private fReEntry As Boolean Private Sub TeamAddsListBox_Click() If Not fReEntry Then fReEntry = True TeamListBox.AddItem TeamAddsListBox.Value TeamAddsListBox.RemoveItem TeamAddsListBox.ListIndex Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Replace What:="#N/A", Replacement:="" Range("TEAMLIST").Sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select fReEntry = False End If End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Tibbetts" wrote in message ... Hoo Boy, this will take some explaining. Yes, TEAMLIST is a named range. All of my named ranges are in CAPS. TeamAddsListBox is populated by the value of a named range ADDLIST which is populated by the value of a named cell that changes based on a selection from a ComboBox. All ListBoxes are SingleSelect and a procedure is triggered by the Click event. The other code is there because I am also populating a range called DROPS in the spreadsheet. I need this as a temp holding area until the DROP button is clicked. The other code was copied from another porcedure and is used to re-sort the list after changes are made. I could overwhelm you with more info if you need it. Thanks for trying to help me out. -- Jim T "Bob Phillips" wrote: Jim, I tried to recreate this and failed miserably. I assume TEAMLIST is a named range in the workbook? How is TeamAddsListBox populated, RowSource or AddItem, and from where? Is TeamAddsListBox a multiselect listbox or single select? I see the point of loop 1 and loope 2, move the selected item form one listbox to another and delete from the former (although it is overkill if it is a single seelct), but I am lost as to what this code aims to do Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value When I ran this bit, TeamListBox filled with many repeated instances on one value. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Tibbetts" wrote in message ... Thanks for the reply Susan. I added "a=nothing" (without the quotes) after the last line but got an error message. -- Jim T "Susan" wrote: try setting your variables to nothing @ the end of the sub....... a=nothing just an idea that might help. susan On Feb 6, 1:32 pm, Jim Tibbetts wrote: OK, I give up. Can someone please tell me why the following macro works perfectly the first time the TeamAddsListBox is clicked, but the second time it is clicked, the TEAMLIST range and the TeamListBox are not updated from the TeamAddsListBox. Private Sub TeamAddsListBox_Click() For a = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(a) Then TeamListBox.AddItem TeamAddsListBox.List(a) End If Next For a = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(a) Then TeamAddsListBox.Selected(a) = False TeamAddsListBox.RemoveItem a End If Next Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value End Sub If I comment out the last line, the TEAMLIST is updated and sorted correctly and the name is added to the TeamListBox, but it is just added onto the end of the list. I need to have the list in the TeamListBox mirror the list in TEAMLIST. The name is removed from the TeamAddsList (as it should be) in either case. I have been stuggling with this all morning. Any help will be greatly appreciated. Thanks, -- Jim T |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro doesn't work twice
Ok, I have found the problem. The TeamListBox2 IS being updated. However,
TeamListBox2 is set for 10 rows because no team can have more than 10 golfers. TEAMLIST is also 10 rows long. Most of the time there are spaces at the end of the list because the team doesn't have 10 golfers. The name being added was being added in the 11th space in the list in TeamListBox2 (whether there were spaces or not) and therefore not showing up in TEAMLIST which is being populated by TeamListBox2, which is then used to repopoulate TeamListBox2 after being sorted. So......how do I get the name to not go to the 11th spot but to go to the first empty space? -- Jim T "Bob Phillips" wrote: Jim, I tried to recreate this and failed miserably. I assume TEAMLIST is a named range in the workbook? How is TeamAddsListBox populated, RowSource or AddItem, and from where? Is TeamAddsListBox a multiselect listbox or single select? I see the point of loop 1 and loope 2, move the selected item form one listbox to another and delete from the former (although it is overkill if it is a single seelct), but I am lost as to what this code aims to do Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value When I ran this bit, TeamListBox filled with many repeated instances on one value. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Tibbetts" wrote in message ... Thanks for the reply Susan. I added "a=nothing" (without the quotes) after the last line but got an error message. -- Jim T "Susan" wrote: try setting your variables to nothing @ the end of the sub....... a=nothing just an idea that might help. susan On Feb 6, 1:32 pm, Jim Tibbetts wrote: OK, I give up. Can someone please tell me why the following macro works perfectly the first time the TeamAddsListBox is clicked, but the second time it is clicked, the TEAMLIST range and the TeamListBox are not updated from the TeamAddsListBox. Private Sub TeamAddsListBox_Click() For a = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(a) Then TeamListBox.AddItem TeamAddsListBox.List(a) End If Next For a = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(a) Then TeamAddsListBox.Selected(a) = False TeamAddsListBox.RemoveItem a End If Next Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value End Sub If I comment out the last line, the TEAMLIST is updated and sorted correctly and the name is added to the TeamListBox, but it is just added onto the end of the list. I need to have the list in the TeamListBox mirror the list in TEAMLIST. The name is removed from the TeamAddsList (as it should be) in either case. I have been stuggling with this all morning. Any help will be greatly appreciated. Thanks, -- Jim T |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro doesn't work twice
If you are using the click event, then you are not going to multiselect (and
you should have the multiselect property set to false). Then this worked for me Private fReEntry As Boolean Private Sub TeamAddsListBox_Click() If fReEntry Then Exit Sub fReEntry = True TeamListBox2.AddItem TeamAddsListBox.Value TeamAddsListBox.RemoveItem TeamAddsListBox.ListIndex fReEntry = False End Sub Private Sub TeamAddsListBox_MouseDown( _ ByVal Button As Integer, ByVal Shift As Integer, _ ByVal X As Single, ByVal Y As Single) TeamAddsListBox.ListIndex = -1 End Sub Private Sub UserForm_Initialize() TeamAddsListBox.AddItem "Item1" TeamAddsListBox.AddItem "Item2" TeamAddsListBox.AddItem "Item3" TeamAddsListBox.AddItem "Item4" TeamAddsListBox.AddItem "Item5" End Sub The mousedown is needed so you can move the last item. -- Regards, Tom Ogilvy "Jim Tibbetts" wrote in message ... Opps - I forgot the code that doesn't work. Here it is. Private Sub TeamAddsListBox_Click() For A = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(A) Then TeamListBox2.AddItem TeamAddsListBox.List(A) End If Next For A = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(A) Then TeamAddsListBox.Selected(A) = False TeamAddsListBox.RemoveItem A End If Next There is more, but the problem is TeamListBox2 isn't being updated. TeamAddsList is being updated properly, though. -- Jim T "Bob Phillips" wrote: Jim, I tried to recreate this and failed miserably. I assume TEAMLIST is a named range in the workbook? How is TeamAddsListBox populated, RowSource or AddItem, and from where? Is TeamAddsListBox a multiselect listbox or single select? I see the point of loop 1 and loope 2, move the selected item form one listbox to another and delete from the former (although it is overkill if it is a single seelct), but I am lost as to what this code aims to do Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value When I ran this bit, TeamListBox filled with many repeated instances on one value. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Tibbetts" wrote in message ... Thanks for the reply Susan. I added "a=nothing" (without the quotes) after the last line but got an error message. -- Jim T "Susan" wrote: try setting your variables to nothing @ the end of the sub....... a=nothing just an idea that might help. susan On Feb 6, 1:32 pm, Jim Tibbetts wrote: OK, I give up. Can someone please tell me why the following macro works perfectly the first time the TeamAddsListBox is clicked, but the second time it is clicked, the TEAMLIST range and the TeamListBox are not updated from the TeamAddsListBox. Private Sub TeamAddsListBox_Click() For a = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(a) Then TeamListBox.AddItem TeamAddsListBox.List(a) End If Next For a = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(a) Then TeamAddsListBox.Selected(a) = False TeamAddsListBox.RemoveItem a End If Next Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value End Sub If I comment out the last line, the TEAMLIST is updated and sorted correctly and the name is added to the TeamListBox, but it is just added onto the end of the list. I need to have the list in the TeamListBox mirror the list in TEAMLIST. The name is removed from the TeamAddsList (as it should be) in either case. I have been stuggling with this all morning. Any help will be greatly appreciated. Thanks, -- Jim T |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro doesn't work twice
Loop through the list and find where you want to place it.
-- Regards, Tom Ogilvy "Jim Tibbetts" wrote in message ... Ok, I have found the problem. The TeamListBox2 IS being updated. However, TeamListBox2 is set for 10 rows because no team can have more than 10 golfers. TEAMLIST is also 10 rows long. Most of the time there are spaces at the end of the list because the team doesn't have 10 golfers. The name being added was being added in the 11th space in the list in TeamListBox2 (whether there were spaces or not) and therefore not showing up in TEAMLIST which is being populated by TeamListBox2, which is then used to repopoulate TeamListBox2 after being sorted. So......how do I get the name to not go to the 11th spot but to go to the first empty space? -- Jim T "Bob Phillips" wrote: Jim, I tried to recreate this and failed miserably. I assume TEAMLIST is a named range in the workbook? How is TeamAddsListBox populated, RowSource or AddItem, and from where? Is TeamAddsListBox a multiselect listbox or single select? I see the point of loop 1 and loope 2, move the selected item form one listbox to another and delete from the former (although it is overkill if it is a single seelct), but I am lost as to what this code aims to do Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value When I ran this bit, TeamListBox filled with many repeated instances on one value. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Tibbetts" wrote in message ... Thanks for the reply Susan. I added "a=nothing" (without the quotes) after the last line but got an error message. -- Jim T "Susan" wrote: try setting your variables to nothing @ the end of the sub....... a=nothing just an idea that might help. susan On Feb 6, 1:32 pm, Jim Tibbetts wrote: OK, I give up. Can someone please tell me why the following macro works perfectly the first time the TeamAddsListBox is clicked, but the second time it is clicked, the TEAMLIST range and the TeamListBox are not updated from the TeamAddsListBox. Private Sub TeamAddsListBox_Click() For a = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(a) Then TeamListBox.AddItem TeamAddsListBox.List(a) End If Next For a = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(a) Then TeamAddsListBox.Selected(a) = False TeamAddsListBox.RemoveItem a End If Next Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value End Sub If I comment out the last line, the TEAMLIST is updated and sorted correctly and the name is added to the TeamListBox, but it is just added onto the end of the list. I need to have the list in the TeamListBox mirror the list in TEAMLIST. The name is removed from the TeamAddsList (as it should be) in either case. I have been stuggling with this all morning. Any help will be greatly appreciated. Thanks, -- Jim T |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro doesn't work twice
Tom - I didn't know whether to post this in this thread or the one you have
been helping Rawce in. So I will post it in both. I figured that my problem with a name being added out of the TeamListBox range would be solved by sorting the list to move blanks to the bottom. Your code to Rawce seemed like it should do that. However, I get an error and it flags QuickSort saying "Sub or Function not defined". How do I define QuickSort? Thanks -- Jim T "Tom Ogilvy" wrote: Loop through the list and find where you want to place it. -- Regards, Tom Ogilvy "Jim Tibbetts" wrote in message ... Ok, I have found the problem. The TeamListBox2 IS being updated. However, TeamListBox2 is set for 10 rows because no team can have more than 10 golfers. TEAMLIST is also 10 rows long. Most of the time there are spaces at the end of the list because the team doesn't have 10 golfers. The name being added was being added in the 11th space in the list in TeamListBox2 (whether there were spaces or not) and therefore not showing up in TEAMLIST which is being populated by TeamListBox2, which is then used to repopoulate TeamListBox2 after being sorted. So......how do I get the name to not go to the 11th spot but to go to the first empty space? -- Jim T "Bob Phillips" wrote: Jim, I tried to recreate this and failed miserably. I assume TEAMLIST is a named range in the workbook? How is TeamAddsListBox populated, RowSource or AddItem, and from where? Is TeamAddsListBox a multiselect listbox or single select? I see the point of loop 1 and loope 2, move the selected item form one listbox to another and delete from the former (although it is overkill if it is a single seelct), but I am lost as to what this code aims to do Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value When I ran this bit, TeamListBox filled with many repeated instances on one value. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Tibbetts" wrote in message ... Thanks for the reply Susan. I added "a=nothing" (without the quotes) after the last line but got an error message. -- Jim T "Susan" wrote: try setting your variables to nothing @ the end of the sub....... a=nothing just an idea that might help. susan On Feb 6, 1:32 pm, Jim Tibbetts wrote: OK, I give up. Can someone please tell me why the following macro works perfectly the first time the TeamAddsListBox is clicked, but the second time it is clicked, the TEAMLIST range and the TeamListBox are not updated from the TeamAddsListBox. Private Sub TeamAddsListBox_Click() For a = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(a) Then TeamListBox.AddItem TeamAddsListBox.List(a) End If Next For a = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(a) Then TeamAddsListBox.Selected(a) = False TeamAddsListBox.RemoveItem a End If Next Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value End Sub If I comment out the last line, the TEAMLIST is updated and sorted correctly and the name is added to the TeamListBox, but it is just added onto the end of the list. I need to have the list in the TeamListBox mirror the list in TEAMLIST. The name is removed from the TeamAddsList (as it should be) in either case. I have been stuggling with this all morning. Any help will be greatly appreciated. Thanks, -- Jim T |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro doesn't work twice
thought I posted the Quicksort function in that thread. If not, here it is:
Sub QuickSort(SortArray, L, R) ' 'Posted by Jim Rech 10/20/98 Excel.Programming 'Modified to sort on first column of a two dimensional array ' Dim i, j, X, Y i = L j = R X = SortArray((L + R) / 2, LBound(SortArray, 2)) While (i <= j) While (SortArray(i, LBound(SortArray, 2)) < X And i < R) i = i + 1 Wend While (X < SortArray(j, LBound(SortArray, 2)) And j L) j = j - 1 Wend If (i <= j) Then Y = SortArray(i, LBound(SortArray, 2)) SortArray(i, LBound(SortArray, 2)) = _ SortArray(j, LBound(SortArray, 2)) SortArray(j, LBound(SortArray, 2)) = Y i = i + 1 j = j - 1 End If Wend If (L < j) Then Call QuickSort(SortArray, L, j) If (i < R) Then Call QuickSort(SortArray, i, R) End Sub But if you are not using multiselect, that may be overkill. -- Regards, Tom Ogilvy "Jim Tibbetts" wrote: Tom - I didn't know whether to post this in this thread or the one you have been helping Rawce in. So I will post it in both. I figured that my problem with a name being added out of the TeamListBox range would be solved by sorting the list to move blanks to the bottom. Your code to Rawce seemed like it should do that. However, I get an error and it flags QuickSort saying "Sub or Function not defined". How do I define QuickSort? Thanks -- Jim T "Tom Ogilvy" wrote: Loop through the list and find where you want to place it. -- Regards, Tom Ogilvy "Jim Tibbetts" wrote in message ... Ok, I have found the problem. The TeamListBox2 IS being updated. However, TeamListBox2 is set for 10 rows because no team can have more than 10 golfers. TEAMLIST is also 10 rows long. Most of the time there are spaces at the end of the list because the team doesn't have 10 golfers. The name being added was being added in the 11th space in the list in TeamListBox2 (whether there were spaces or not) and therefore not showing up in TEAMLIST which is being populated by TeamListBox2, which is then used to repopoulate TeamListBox2 after being sorted. So......how do I get the name to not go to the 11th spot but to go to the first empty space? -- Jim T "Bob Phillips" wrote: Jim, I tried to recreate this and failed miserably. I assume TEAMLIST is a named range in the workbook? How is TeamAddsListBox populated, RowSource or AddItem, and from where? Is TeamAddsListBox a multiselect listbox or single select? I see the point of loop 1 and loope 2, move the selected item form one listbox to another and delete from the former (although it is overkill if it is a single seelct), but I am lost as to what this code aims to do Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value When I ran this bit, TeamListBox filled with many repeated instances on one value. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Tibbetts" wrote in message ... Thanks for the reply Susan. I added "a=nothing" (without the quotes) after the last line but got an error message. -- Jim T "Susan" wrote: try setting your variables to nothing @ the end of the sub....... a=nothing just an idea that might help. susan On Feb 6, 1:32 pm, Jim Tibbetts wrote: OK, I give up. Can someone please tell me why the following macro works perfectly the first time the TeamAddsListBox is clicked, but the second time it is clicked, the TEAMLIST range and the TeamListBox are not updated from the TeamAddsListBox. Private Sub TeamAddsListBox_Click() For a = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(a) Then TeamListBox.AddItem TeamAddsListBox.List(a) End If Next For a = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(a) Then TeamAddsListBox.Selected(a) = False TeamAddsListBox.RemoveItem a End If Next Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value End Sub If I comment out the last line, the TEAMLIST is updated and sorted correctly and the name is added to the TeamListBox, but it is just added onto the end of the list. I need to have the list in the TeamListBox mirror the list in TEAMLIST. The name is removed from the TeamAddsList (as it should be) in either case. I have been stuggling with this all morning. Any help will be greatly appreciated. Thanks, -- Jim T |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro doesn't work twice
Of course you are right. It was there, I just didn't see it. But, now that I
see it, how can that code be skinnied down to only sort the single column of data in TeamListBox2 before I use it to populate TEAMLIST in the workbook? Thanks for your help Tom, you are very patient. -- Jim T "Tom Ogilvy" wrote: thought I posted the Quicksort function in that thread. If not, here it is: Sub QuickSort(SortArray, L, R) ' 'Posted by Jim Rech 10/20/98 Excel.Programming 'Modified to sort on first column of a two dimensional array ' Dim i, j, X, Y i = L j = R X = SortArray((L + R) / 2, LBound(SortArray, 2)) While (i <= j) While (SortArray(i, LBound(SortArray, 2)) < X And i < R) i = i + 1 Wend While (X < SortArray(j, LBound(SortArray, 2)) And j L) j = j - 1 Wend If (i <= j) Then Y = SortArray(i, LBound(SortArray, 2)) SortArray(i, LBound(SortArray, 2)) = _ SortArray(j, LBound(SortArray, 2)) SortArray(j, LBound(SortArray, 2)) = Y i = i + 1 j = j - 1 End If Wend If (L < j) Then Call QuickSort(SortArray, L, j) If (i < R) Then Call QuickSort(SortArray, i, R) End Sub But if you are not using multiselect, that may be overkill. -- Regards, Tom Ogilvy "Jim Tibbetts" wrote: Tom - I didn't know whether to post this in this thread or the one you have been helping Rawce in. So I will post it in both. I figured that my problem with a name being added out of the TeamListBox range would be solved by sorting the list to move blanks to the bottom. Your code to Rawce seemed like it should do that. However, I get an error and it flags QuickSort saying "Sub or Function not defined". How do I define QuickSort? Thanks -- Jim T "Tom Ogilvy" wrote: Loop through the list and find where you want to place it. -- Regards, Tom Ogilvy "Jim Tibbetts" wrote in message ... Ok, I have found the problem. The TeamListBox2 IS being updated. However, TeamListBox2 is set for 10 rows because no team can have more than 10 golfers. TEAMLIST is also 10 rows long. Most of the time there are spaces at the end of the list because the team doesn't have 10 golfers. The name being added was being added in the 11th space in the list in TeamListBox2 (whether there were spaces or not) and therefore not showing up in TEAMLIST which is being populated by TeamListBox2, which is then used to repopoulate TeamListBox2 after being sorted. So......how do I get the name to not go to the 11th spot but to go to the first empty space? -- Jim T "Bob Phillips" wrote: Jim, I tried to recreate this and failed miserably. I assume TEAMLIST is a named range in the workbook? How is TeamAddsListBox populated, RowSource or AddItem, and from where? Is TeamAddsListBox a multiselect listbox or single select? I see the point of loop 1 and loope 2, move the selected item form one listbox to another and delete from the former (although it is overkill if it is a single seelct), but I am lost as to what this code aims to do Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value When I ran this bit, TeamListBox filled with many repeated instances on one value. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Tibbetts" wrote in message ... Thanks for the reply Susan. I added "a=nothing" (without the quotes) after the last line but got an error message. -- Jim T "Susan" wrote: try setting your variables to nothing @ the end of the sub....... a=nothing just an idea that might help. susan On Feb 6, 1:32 pm, Jim Tibbetts wrote: OK, I give up. Can someone please tell me why the following macro works perfectly the first time the TeamAddsListBox is clicked, but the second time it is clicked, the TEAMLIST range and the TeamListBox are not updated from the TeamAddsListBox. Private Sub TeamAddsListBox_Click() For a = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(a) Then TeamListBox.AddItem TeamAddsListBox.List(a) End If Next For a = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(a) Then TeamAddsListBox.Selected(a) = False TeamAddsListBox.RemoveItem a End If Next Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value End Sub If I comment out the last line, the TEAMLIST is updated and sorted correctly and the name is added to the TeamListBox, but it is just added onto the end of the list. I need to have the list in the TeamListBox mirror the list in TEAMLIST. The name is removed from the TeamAddsList (as it should be) in either case. I have been stuggling with this all morning. Any help will be greatly appreciated. Thanks, -- Jim T |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro doesn't work twice
This has the original version which does a 1D array:
Sub UpdateFileLB() Dim Fname As String, FileArray() As String Dim FCounter As Long Fname = Dir("C:\excel\files\*.xls") Do While Fname < "" FCounter = FCounter + 1 ReDim Preserve FileArray(1 To FCounter) FileArray(FCounter) = Fname Fname = Dir() Loop QuickSort FileArray, 1, FCounter DialogSheets("Dialog1").ListBoxes("List Box 5").List = FileArray End Sub Sub QuickSort(SortArray, L As Long, R As Long) Dim I As Long, J As Long, x As Variant, Y As Variant I = L J = R x = SortArray((L + R) / 2) While (I <= J) While (SortArray(I) < x And I < R) I = I + 1 Wend While (x < SortArray(J) And J L) J = J - 1 Wend If (I <= J) Then Y = SortArray(I) SortArray(I) = SortArray(J) SortArray(J) = Y I = I + 1 J = J - 1 End If Wend If (L < J) Then Call QuickSort(SortArray, L, J) If (I < R) Then Call QuickSort(SortArray, I, R) End Sub -- Jim Rech -- Regards, Tom Ogilvy "Jim Tibbetts" wrote in message ... Of course you are right. It was there, I just didn't see it. But, now that I see it, how can that code be skinnied down to only sort the single column of data in TeamListBox2 before I use it to populate TEAMLIST in the workbook? Thanks for your help Tom, you are very patient. -- Jim T "Tom Ogilvy" wrote: thought I posted the Quicksort function in that thread. If not, here it is: Sub QuickSort(SortArray, L, R) ' 'Posted by Jim Rech 10/20/98 Excel.Programming 'Modified to sort on first column of a two dimensional array ' Dim i, j, X, Y i = L j = R X = SortArray((L + R) / 2, LBound(SortArray, 2)) While (i <= j) While (SortArray(i, LBound(SortArray, 2)) < X And i < R) i = i + 1 Wend While (X < SortArray(j, LBound(SortArray, 2)) And j L) j = j - 1 Wend If (i <= j) Then Y = SortArray(i, LBound(SortArray, 2)) SortArray(i, LBound(SortArray, 2)) = _ SortArray(j, LBound(SortArray, 2)) SortArray(j, LBound(SortArray, 2)) = Y i = i + 1 j = j - 1 End If Wend If (L < j) Then Call QuickSort(SortArray, L, j) If (i < R) Then Call QuickSort(SortArray, i, R) End Sub But if you are not using multiselect, that may be overkill. -- Regards, Tom Ogilvy "Jim Tibbetts" wrote: Tom - I didn't know whether to post this in this thread or the one you have been helping Rawce in. So I will post it in both. I figured that my problem with a name being added out of the TeamListBox range would be solved by sorting the list to move blanks to the bottom. Your code to Rawce seemed like it should do that. However, I get an error and it flags QuickSort saying "Sub or Function not defined". How do I define QuickSort? Thanks -- Jim T "Tom Ogilvy" wrote: Loop through the list and find where you want to place it. -- Regards, Tom Ogilvy "Jim Tibbetts" wrote in message ... Ok, I have found the problem. The TeamListBox2 IS being updated. However, TeamListBox2 is set for 10 rows because no team can have more than 10 golfers. TEAMLIST is also 10 rows long. Most of the time there are spaces at the end of the list because the team doesn't have 10 golfers. The name being added was being added in the 11th space in the list in TeamListBox2 (whether there were spaces or not) and therefore not showing up in TEAMLIST which is being populated by TeamListBox2, which is then used to repopoulate TeamListBox2 after being sorted. So......how do I get the name to not go to the 11th spot but to go to the first empty space? -- Jim T "Bob Phillips" wrote: Jim, I tried to recreate this and failed miserably. I assume TEAMLIST is a named range in the workbook? How is TeamAddsListBox populated, RowSource or AddItem, and from where? Is TeamAddsListBox a multiselect listbox or single select? I see the point of loop 1 and loope 2, move the selected item form one listbox to another and delete from the former (although it is overkill if it is a single seelct), but I am lost as to what this code aims to do Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value When I ran this bit, TeamListBox filled with many repeated instances on one value. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Tibbetts" wrote in message ... Thanks for the reply Susan. I added "a=nothing" (without the quotes) after the last line but got an error message. -- Jim T "Susan" wrote: try setting your variables to nothing @ the end of the sub....... a=nothing just an idea that might help. susan On Feb 6, 1:32 pm, Jim Tibbetts wrote: OK, I give up. Can someone please tell me why the following macro works perfectly the first time the TeamAddsListBox is clicked, but the second time it is clicked, the TEAMLIST range and the TeamListBox are not updated from the TeamAddsListBox. Private Sub TeamAddsListBox_Click() For a = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(a) Then TeamListBox.AddItem TeamAddsListBox.List(a) End If Next For a = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(a) Then TeamAddsListBox.Selected(a) = False TeamAddsListBox.RemoveItem a End If Next Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value End Sub If I comment out the last line, the TEAMLIST is updated and sorted correctly and the name is added to the TeamListBox, but it is just added onto the end of the list. I need to have the list in the TeamListBox mirror the list in TEAMLIST. The name is removed from the TeamAddsList (as it should be) in either case. I have been stuggling with this all morning. Any help will be greatly appreciated. Thanks, -- Jim T |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro doesn't work twice
Thanks Tom. Boy, that's a lot of code just to sort a list in a ListBox. I
will try it. Also, I have posted another question in another thread we had before "Moving name from 1 ListBox to another". When you get time could you check it out and see if you think it will help me? Thanks again for ALL your help. -- Jim T "Tom Ogilvy" wrote: This has the original version which does a 1D array: Sub UpdateFileLB() Dim Fname As String, FileArray() As String Dim FCounter As Long Fname = Dir("C:\excel\files\*.xls") Do While Fname < "" FCounter = FCounter + 1 ReDim Preserve FileArray(1 To FCounter) FileArray(FCounter) = Fname Fname = Dir() Loop QuickSort FileArray, 1, FCounter DialogSheets("Dialog1").ListBoxes("List Box 5").List = FileArray End Sub Sub QuickSort(SortArray, L As Long, R As Long) Dim I As Long, J As Long, x As Variant, Y As Variant I = L J = R x = SortArray((L + R) / 2) While (I <= J) While (SortArray(I) < x And I < R) I = I + 1 Wend While (x < SortArray(J) And J L) J = J - 1 Wend If (I <= J) Then Y = SortArray(I) SortArray(I) = SortArray(J) SortArray(J) = Y I = I + 1 J = J - 1 End If Wend If (L < J) Then Call QuickSort(SortArray, L, J) If (I < R) Then Call QuickSort(SortArray, I, R) End Sub -- Jim Rech -- Regards, Tom Ogilvy "Jim Tibbetts" wrote in message ... Of course you are right. It was there, I just didn't see it. But, now that I see it, how can that code be skinnied down to only sort the single column of data in TeamListBox2 before I use it to populate TEAMLIST in the workbook? Thanks for your help Tom, you are very patient. -- Jim T "Tom Ogilvy" wrote: thought I posted the Quicksort function in that thread. If not, here it is: Sub QuickSort(SortArray, L, R) ' 'Posted by Jim Rech 10/20/98 Excel.Programming 'Modified to sort on first column of a two dimensional array ' Dim i, j, X, Y i = L j = R X = SortArray((L + R) / 2, LBound(SortArray, 2)) While (i <= j) While (SortArray(i, LBound(SortArray, 2)) < X And i < R) i = i + 1 Wend While (X < SortArray(j, LBound(SortArray, 2)) And j L) j = j - 1 Wend If (i <= j) Then Y = SortArray(i, LBound(SortArray, 2)) SortArray(i, LBound(SortArray, 2)) = _ SortArray(j, LBound(SortArray, 2)) SortArray(j, LBound(SortArray, 2)) = Y i = i + 1 j = j - 1 End If Wend If (L < j) Then Call QuickSort(SortArray, L, j) If (i < R) Then Call QuickSort(SortArray, i, R) End Sub But if you are not using multiselect, that may be overkill. -- Regards, Tom Ogilvy "Jim Tibbetts" wrote: Tom - I didn't know whether to post this in this thread or the one you have been helping Rawce in. So I will post it in both. I figured that my problem with a name being added out of the TeamListBox range would be solved by sorting the list to move blanks to the bottom. Your code to Rawce seemed like it should do that. However, I get an error and it flags QuickSort saying "Sub or Function not defined". How do I define QuickSort? Thanks -- Jim T "Tom Ogilvy" wrote: Loop through the list and find where you want to place it. -- Regards, Tom Ogilvy "Jim Tibbetts" wrote in message ... Ok, I have found the problem. The TeamListBox2 IS being updated. However, TeamListBox2 is set for 10 rows because no team can have more than 10 golfers. TEAMLIST is also 10 rows long. Most of the time there are spaces at the end of the list because the team doesn't have 10 golfers. The name being added was being added in the 11th space in the list in TeamListBox2 (whether there were spaces or not) and therefore not showing up in TEAMLIST which is being populated by TeamListBox2, which is then used to repopoulate TeamListBox2 after being sorted. So......how do I get the name to not go to the 11th spot but to go to the first empty space? -- Jim T "Bob Phillips" wrote: Jim, I tried to recreate this and failed miserably. I assume TEAMLIST is a named range in the workbook? How is TeamAddsListBox populated, RowSource or AddItem, and from where? Is TeamAddsListBox a multiselect listbox or single select? I see the point of loop 1 and loope 2, move the selected item form one listbox to another and delete from the former (although it is overkill if it is a single seelct), but I am lost as to what this code aims to do Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value When I ran this bit, TeamListBox filled with many repeated instances on one value. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jim Tibbetts" wrote in message ... Thanks for the reply Susan. I added "a=nothing" (without the quotes) after the last line but got an error message. -- Jim T "Susan" wrote: try setting your variables to nothing @ the end of the sub....... a=nothing just an idea that might help. susan On Feb 6, 1:32 pm, Jim Tibbetts wrote: OK, I give up. Can someone please tell me why the following macro works perfectly the first time the TeamAddsListBox is clicked, but the second time it is clicked, the TEAMLIST range and the TeamListBox are not updated from the TeamAddsListBox. Private Sub TeamAddsListBox_Click() For a = 0 To TeamAddsListBox.ListCount - 1 If TeamAddsListBox.Selected(a) Then TeamListBox.AddItem TeamAddsListBox.List(a) End If Next For a = TeamAddsListBox.ListCount - 1 To 0 Step -1 If TeamAddsListBox.Selected(a) Then TeamAddsListBox.Selected(a) = False TeamAddsListBox.RemoveItem a End If Next Range("TEAMLIST").Value = TeamListBox.List Range("TEAMLIST").Select Selection.Replace What:="#N/A", Replacement:="" Range("TEAMLIST").sort Key1:=Range("TEAMLIST"), Order1:=xlAscending Range("A84").Select TeamListBox.List = Range("TEAMLIST").Value End Sub If I comment out the last line, the TEAMLIST is updated and sorted correctly and the name is added to the TeamListBox, but it is just added onto the end of the list. I need to have the list in the TeamListBox mirror the list in TEAMLIST. The name is removed from the TeamAddsList (as it should be) in either case. I have been stuggling with this all morning. Any help will be greatly appreciated. Thanks, -- Jim T |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to update a column in a work based on another work sheet | New Users to Excel | |||
| Macro works Macro does not work | Excel Discussion (Misc queries) | |||
| If I have a work sheet protected and try to run a macro to hide rows or columns it won't work. Correct? | Excel Programming | |||
| how do you get (end down) to work in a macro? | Excel Worksheet Functions | |||
| why doesn't macro work? | Excel Programming | |||
Linear Mode
