Remove listbox item

[Zack Barresse]

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.

[TonyJollans]

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]

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]

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]

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]

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]

Arrgghh! You got it! Now that was just frustrating.

Thanks Jake!!

[Jacob Hilderbrand]

You're Welcome

Take Care

[Andy Pope]

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]

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]

Hi firefytr,

Removing ALL the items is easier.

Listbox1.Clear

Cheers
Andy

[Zack Barresse]

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]

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]

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]

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]

Hmmm, I get an unspecified error here ...

[vba] ListBox1.RemoveItem lngIndex2[/vba]

[Andy Pope]

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]

That's just beautiful Andy! Works like a charm! I can't thank you enough, it's extremely appreciated!!

[Andy Pope]

You're welcome