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.
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.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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.
Enjoy,
Tony
---------------------------------------------------------------
Give a man a fish and he'll eat for a day.
Teach him how to fish and he'll sit in a boat and drink beer all day.
I'm (slowly) building my own site: www.WordArticles.com
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.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Btw, what I've got so far is ...
I'm not sure where I'm going wrong at here, it doens't work.ListBox2.List(ListBox2.ListIndex).RemoveItem
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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 ..
I'll check in laterFor i = lbound(listbox1.list) to ubound(listbox1.list) magbox listbox1.list(i) ' or whatever you want to do Next i
Enjoy,
Tony
---------------------------------------------------------------
Give a man a fish and he'll eat for a day.
Teach him how to fish and he'll sit in a boat and drink beer all day.
I'm (slowly) building my own site: www.WordArticles.com
You're close. Try this:Originally Posted by firefytr
ListBox2.RemoveItem (ListBox2.ListIndex)
Arrgghh! You got it! Now that was just frustrating.
Thanks Jake!!
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
You're Welcome
Take Care
Hi all,
If you want to remove multiple selected items from the listbox then remember to go through the list backwards.
CheersFor intIndex = ListBox1.ListCount - 1 To 0 Step -1 If ListBox1.Selected(intIndex) Then ListBox1.RemoveItem intIndex End If Next
Andy
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. )
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Hi firefytr,
Removing ALL the items is easier.
Listbox1.Clear
Cheers
Andy
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?
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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
Cheers
Andy
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.
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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
Cheers
Andy
Hmmm, I get an unspecified error here ...
[vba] ListBox1.RemoveItem lngIndex2[/vba]
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
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
Cheers
Andy
That's just beautiful Andy! Works like a charm! I can't thank you enough, it's extremely appreciated!!
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
You're welcome
Cheers
Andy