Excel VBA delete item from ListBox

0

I'm having trouble deleting an item from a listbox . The code works like this: it will have listbox with the list, and when the user wants to delete an item, it clicks a button that opens another userform , in that userform the user enters the number of the item to be excluded in listbox , along with a msgbox to request confirmation of deletion.

Private Sub CommandButton1_Click()
    Dim curso

    resposta = MsgBox("Deseja excluir o curso?", vbYesNo + vbQuestion, "Excluir?")
    If resposta = vbYes Then
        curso = TextBox1.Text
        UserForm2.ListBox1.RemoveItem (curso)
    End If
End Sub

For the sake of functionality, I'd prefer the code to work like this, with two different acknowledgments (explicitly informing the item number to be deleted and msgbox ).

I am open to opinions.

    
asked by anonymous 03.05.2017 / 16:30

2 answers

1

To select one item at a time

This code removes the selected item

'''
'Remover item
'''  
'Remove item selecionado da lista 1

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

Clear ListBox

Me.ListBox1.Clear

Confirmation before deleting item

Private Sub CommandButton1_Click()
    '''
    'Remover item
    '''
    Dim counter As Integer
    'Remove item selecionado da lista 1
    With Me
        For i = 0 To .ListBox1.ListCount - 1
            If .ListBox1.Selected(i) Then
                resposta = MsgBox("Deseja excluir o item: " & .ListBox1.List(i) & "?", vbYesNo + vbQuestion, "Excluir?")
                If vbYes Then .ListBox1.RemoveItem (i)
            End If
        Next i

    End With
End Sub

Multiselection

There are three options for Multiselect :

  • ListBox.MultiSelect = 0 : Select only one element. (Item removal methods explained earlier)
  • ListBox.MultiSelect = 1 : Click the item or press the space bar to select multiple items
  • ListBox.MultiSelect = 2 : Press Shift and Ctrl to select multiple items

Code:

    Private Sub UserForm_Initialize()
        Me.ListBox1.MultiSelect = Número_Multiselect
    End Sub

Remove / Delete Multiple items with individual confirmation

Private Sub CommandButton1_Click()
    '''
    'Remover item
    '''
    'Remove item selecionado da lista 1
    With Me
        For i = .ListBox1.ListCount - 1 To 0 Step -1
            If .ListBox1.Selected(i) Then
                resposta = MsgBox("Deseja excluir o item: " & .ListBox1.List(i) & "?", vbYesNo + vbQuestion, "Excluir?")
                If vbYes Then .ListBox1.RemoveItem (i)
            End If
        Next i
    End With
End Sub

Remove / Delete Multiple items with multiple confirmation

Private Sub CommandButton1_Click()
    '''
    'Remover item
    '''
    'Remove item selecionado da lista 1
    Dim i As Long, contador As Long
    Dim vetor() As Variant
    With Me
        For i = .ListBox1.ListCount - 1 To 0 Step -1
            If .ListBox1.Selected(i) Then
                ReDim Preserve vetor(contador)
                msg = msg & vbCrLf & .ListBox1.List(i)
                vetor(contador) = i
                contador = contador + 1
            End If
        Next i
        resposta = MsgBox("Deseja excluir os itens a seguir? " & msg, vbYesNo + vbQuestion, "Excluir?")
        If vbYes Then
            For i = LBound(vetor) To UBound(vetor)
                .ListBox1.RemoveItem (vetor(i))
            Next i
        End If
    End With
End Sub

By Item Number:

In case you want to remove by item number, be aware that the list starts at zero.

For an example where the ListBox is in Userform1 and the item number entry is in Userform2, with a command button and a text box:

Userform1

Private Sub CommandButton1_Click()
    UserForm2.Show
End Sub

Userform2

Private Sub CommandButton1_Click()
    '''
    'Remover item
    '''
    'Remove item selecionado da lista 1
    With UserForm1
         i = Me.TextBox1
        resposta = MsgBox("Deseja excluir o item: " & .ListBox1.List(i) & "?", vbYesNo + vbQuestion, "Excluir?")
        If vbYes Then .ListBox1.RemoveItem (i)
    End With

    Me.Hide
    Unload Me
End Sub

List starting at 1

If you want the list to start at 1, the Userform2 code looks like this:

Private Sub CommandButton1_Click()
    With UserForm1
         i = Me.TextBox1
        resposta = MsgBox("Deseja excluir o item: " & .ListBox1.List(i - 1) & "?", vbYesNo + vbQuestion, "Excluir?")
        If vbYes Then .ListBox1.RemoveItem (i - 1)
    End With

    Me.Hide
    Unload Me
End Sub
    
06.09.2017 / 22:57
0
Private Sub CommandButton1_Click()
    ListBox1.AddItem "teste"

End Sub

Private Sub CommandButton2_Click()

    Dim curso

    resposta = MsgBox("Deseja excluir o curso?", vbYesNo + vbQuestion, "Excluir?")
    If resposta = vbYes Then
        curso = TextBox1.Text
        UserForm1.ListBox1.RemoveItem (curso)
    End If

End Sub

I tested this code here and it worked. The only difference is that the text box that informs the position of the item to be deleted is on the same UserForm.

    
04.05.2017 / 02:22