PDA

View Full Version : Solved: Remove listbox item



Zack Barresse
08-18-2004, 10:58 AM
Hi,

What would be the proper way to remove a selected item from a listbox? I can clear the entire thing, but I just want to pick and choose which items are displayed. I've got the add part down, but I can't seem to get the remove part. :bink:

TonyJollans
08-18-2004, 11:31 AM
Hi Zack,

The RemoveItem Method is the one you want.

It uses index numbers - there is no way to access listbox entries directly by the text.

If you want to find one with particular text you need to run through the listbox.list array checking each one - it's a standard array so you can use UBound to find the last entry, or you can use listbox.listcount-1.

Zack Barresse
08-18-2004, 11:44 AM
Thanks Tony!

For my looping I'd been using For i = 1 to Listbox1.ListCount. Seems to be doing the trick. Would it be more efficient to do somthing in an array? I'm not all that familiar w/ UBound.

Zack Barresse
08-18-2004, 11:45 AM
Btw, what I've got so far is ...

ListBox2.List(ListBox2.ListIndex).RemoveItem

I'm not sure where I'm going wrong at here, it doens't work.

TonyJollans
08-18-2004, 12:00 PM
Hi Zack,

I've just ordered a Chinese, so real quick before I go for it ..

1. ListIndex gives you the selected item - which might be what you want, I don't know
2. For looping you should use 0 to listbox1.Listcount - 1
3. Or, for looping, use ..

For i = lbound(listbox1.list) to ubound(listbox1.list)
magbox listbox1.list(i) ' or whatever you want to do
Next i

I'll check in later

Jacob Hilderbrand
08-18-2004, 03:31 PM
Btw, what I've got so far is ...

ListBox2.List(ListBox2.ListIndex).RemoveItem

I'm not sure where I'm going wrong at here, it doens't work.
You're close. Try this:


ListBox2.RemoveItem (ListBox2.ListIndex)

Zack Barresse
08-18-2004, 03:35 PM
Arrgghh! You got it! Now that was just frustrating. :D

Thanks Jake!!

Jacob Hilderbrand
08-18-2004, 03:43 PM
You're Welcome

Take Care

Andy Pope
08-19-2004, 06:01 AM
Hi all,

If you want to remove multiple selected items from the listbox then remember to go through the list backwards.


For intIndex = ListBox1.ListCount - 1 To 0 Step -1
If ListBox1.Selected(intIndex) Then
ListBox1.RemoveItem intIndex
End If
Next

Cheers
Andy

Zack Barresse
08-19-2004, 08:13 AM
Thanks for that Andy! Excellent!

Another related question, if I wanted to remove all of the items from the listbox, what would be the loop for that? I can only seem to do it on the selected item. (I'm slow, but learning. :) )

Andy Pope
08-19-2004, 08:23 AM
Hi firefytr,

Removing ALL the items is easier.

Listbox1.Clear

Cheers
Andy

Zack Barresse
08-19-2004, 08:27 AM
Oh gosh, silly me, I knew that!

Actually (and I'm sorry) I didn't explain too well. I still want to loop through the list. What I need to do is, for each entry in the listbox2, find it in sheet2 column A and delete it (shifting up), also delete it from Listbox1, then delete it from listbox2. Does that make sense?

Andy Pope
08-20-2004, 02:12 PM
Hi firefytr,

Hopefully I have understood.

Create a userform with 2 listboxes and a commandbutton.
The Initialize event populates both listboxes with the contents of column A.
The commandbutton button goes through listbox2 backwards. When it finds a selected item it then tries to locate it in column A, if found the cell is deleted.
It then goes through listbox1 forward this time as we can only delete 1 item. And delete is matched. Finally we delete the selected item from listbox2.
Continue through all selections in listbox2.


Private Sub CommandButton1_Click()

Dim lngIndex As Long
Dim lngIndex2 As Long
Dim rngFind As Range

For lngIndex = ListBox2.ListCount - 1 To 0 Step -1
If ListBox2.Selected(lngIndex) Then
Set rngFind = Worksheets("Sheet2").Range("A:A").Find(ListBox2.List(lngIndex))
If Not rngFind Is Nothing Then rngFind.Delete xlShiftUp
For lngIndex2 = 0 To ListBox1.ListCount - 1
If ListBox1.List(lngIndex2) = ListBox1.List(lngIndex) Then
ListBox1.RemoveItem lngIndex2
Exit For
End If
Next
ListBox2.RemoveItem lngIndex
End If
Next

End Sub

Private Sub UserForm_Initialize()

Dim rngCell As Range

For Each rngCell In Worksheets("Sheet2").Range("A:A")
If rngCell = "" Then Exit For
ListBox1.AddItem rngCell.Value
ListBox2.AddItem rngCell.Value
Next
End Sub

Zack Barresse
08-20-2004, 02:31 PM
Very good Andy! Thank you! The only question I have left is could I do this for more than just the selected List item in the second ListBox? Like if I wanted to do it for every entry in that ListBox.

Andy Pope
08-20-2004, 02:36 PM
Yes just remove the IF THEN test,



For lngIndex = ListBox2.ListCount - 1 To 0 Step -1
Set rngFind = Worksheets("Sheet2").Range("A:A").Find(ListBox2.List(lngIndex))
If Not rngFind Is Nothing Then rngFind.Delete xlShiftUp
For lngIndex2 = 0 To ListBox1.ListCount - 1
If ListBox1.List(lngIndex2) = ListBox1.List(lngIndex) Then
ListBox1.RemoveItem lngIndex2
Exit For
End If
Next
ListBox2.RemoveItem lngIndex
Next

Zack Barresse
08-20-2004, 02:45 PM
Hmmm, I get an unspecified error here ...

ListBox1.RemoveItem lngIndex2

Andy Pope
08-20-2004, 02:58 PM
Sorry, late night typo!


For lngIndex = ListBox2.ListCount - 1 To 0 Step -1
Set rngFind = Worksheets("Sheet2").Range("A:A").Find(ListBox2.List(lngIndex))
If Not rngFind Is Nothing Then rngFind.Delete xlShiftUp
For lngIndex2 = 0 To ListBox1.ListCount - 1
' change test to listbox2
If ListBox1.List(lngIndex2) = ListBox2.List(lngIndex) Then
ListBox1.RemoveItem lngIndex2
Exit For
End If
Next
ListBox2.RemoveItem lngIndex
Next

Zack Barresse
08-20-2004, 03:06 PM
That's just beautiful Andy! Works like a charm! I can't thank you enough, it's extremely appreciated!!

Andy Pope
08-20-2004, 03:09 PM
You're welcome