Can't remove an item from listbox excel vba

Question

I'm trying to fill a listbox in Excel VBA and then after select some itens exclude then from the list. But I keep getting the error '-2147467259 (80004005)'.

I code the following:

Private Sub CommandButton1_Click()

ListBox1.ColumnCount = 1
ListBox1.RowSource = "Planilha1!B3:B11"
ListBox1.Font.Size = 10
ListBox1.Font.Name = "Verdana"

End Sub

Private Sub CommandButton3_Click()

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

End Sub

There's no direct approach - you'll have to decide yourself: either binding to a RowSource or flexibilize the listbox contents by separating them from your row source. — T.M.
– T.M., Commented Mar 4, 2022 at 15:55

Toddleson · Accepted Answer · 2022-03-04 16:06:47Z

1

You could swap .RowSource for .List. The .List property accepts 2D arrays of values. So you could load the values in with .List = Worksheets("Planilha1").Range("B3:B11").Value. And then RemoveItem will work.

Private Sub CommandButton1_Click()

ListBox1.ColumnCount = 1
ListBox1.List = Worksheets("Planilha1").Range("B3:B11").Value
ListBox1.Font.Size = 10
ListBox1.Font.Name = "Verdana"

End Sub

Private Sub CommandButton3_Click()

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

End Sub

answered Mar 4, 2022 at 16:06

Toddleson

4,4852 gold badges9 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TinMan · Accepted Answer · 2022-03-04 16:05:54Z

0

Assigning the Range values to the ListBox1's list will allow you to remove items from the ListBox1.

Demo

Private Sub UserForm_Initialize()
    With ListBox1
        .List = wsPlanilha1.Range("Planilha1!B3:B11").Value
        .ColumnCount = 1
        .Font.Size = 10
        .Font.Name = "Verdana"
        
        .RemoveItem 4
        .RemoveItem 2
    End With
End Sub

Function wsPlanilha1() As Worksheet
    Set wsPlanilha1 = ThisWorkbook.Worksheets("Planilha1")
End Function

Result

Demo

answered Mar 4, 2022 at 16:05

TinMan

7,8142 gold badges13 silver badges24 bronze badges

2 Comments

Toddleson Over a year ago

Ah, you beat me by 1 minute. Interesting idea with the worksheet function, but why separate it like that? Is there a benefit?

TinMan Over a year ago

@Toddleson the main reason is it is easier that explaining code names. I also have a personal macro to creates a module with all a function for each worksheet. If I pickup someone else's project I don't have to modify there structure.

Collectives™ on Stack Overflow

Can't remove an item from listbox excel vba

2 Answers 2

Comments

Demo

Result

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Demo

Result

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related