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
|