Can't remove an item from listbox excel vba-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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