Deleting data selected items listbox

[Bandito1]

Hi everyone,

I got this listbox named lstSearchResults.
The data in it comes through VBA from the worksheet "Telephone".
I would like to have a button to delete items in the listbox that i select.

I found this code;

Private Sub cmdDeleteContact_Click()
Dim i As Long


With Me.lstSearchResults
For i = .ListCount - 1 To 0 Step -1
If .Selected(i) = True Then
.RemoveItem i
End If
Next i
End With
End Sub

The item in the listbox gets deleted but i would like that the data also gets deleted in the worksheet.
How can i add this?

 

[sadboy309]

You need find row of sheet (j) how with (i) on lstSearchResults. Then you convert (i) for (j), so you can use (j) to Delete cells on Sheet.

'Delete 1 row
Rows(j).Delete

'Or clear cell
Range("B" & j).ClearContents

 

[Trebor76]

Hi Bandito1,

This should do the job:

Option Explicit
Private Sub cmdDeleteContact_Click()

    Dim i As Long
    Dim lngMyRow As Long
    
    Application.ScreenUpdating = False

    With Me.lstSearchResults
        For i = .ListCount - 1 To 0 Step -1
            If .Selected(i) = True Then
                If IsNumeric(.List(i)) = True Then
                    lngMyRow = Evaluate("IFERROR(MATCH(" & .List(i) & ",Telephone!A:A,0),0)")
                Else
                    lngMyRow = Evaluate("IFERROR(MATCH(""" & .List(i) & """,Telephone!A:A,0),0)")
                End If
                If lngMyRow > 0 Then
                    Sheets("Telephone").Rows(lngMyRow).EntireRow.Delete
                    .RemoveItem i
                End If
            End If
        Next i
    End With
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[Trebor76]

I should have mentioned that the code looks for the phone numbers in column A (change to suit) and will only delete one matching entry (they should all be unique so that shouldn't be an issue).

 

[Bandito1]

Hello Robert and and Sadboy,

Thanks for your replies and time.
The code seems like to work, thank you very much.

It happens that i have names in my list without phone number. Rest of the numbers are unique.
I was afraid the code would crash if i try to delete someone without phonenumber but that doesn't happen.

 

[Bandito1]

Hi guys,

I tested the code out but when i delete the last row in the listbox the whole list gets deleted.
So when there are 5 items in it i can safely delete 1 - 4 but when i delete #5 everything gets deleted at once..
You know where the mistake is being made?

Private Sub cmdDeleteContact_Click()

    Dim i As Long
    Dim lngMyRow As Long
    Dim varResponse As Variant
    
varResponse = MsgBox("Are you sure you want to delete the selected person?", vbYesNo, "Confirmation")


If varResponse <> vbYes Then Exit Sub


    Application.ScreenUpdating = False


    With Me.lstSearchResults
        For i = .ListCount - 1 To 0 Step -1
            If .Selected(i) = True Then
                If IsNumeric(.List(i)) = True Then
                    lngMyRow = Evaluate("IFERROR(MATCH(" & .List(i) & ",Telephone!A:A,0),0)")
                Else
                    lngMyRow = Evaluate("IFERROR(MATCH(""" & .List(i) & """,Telephone!A:A,0),0)")
                End If
                If lngMyRow > 0 Then
                    Sheets("Telephone").Rows(lngMyRow).EntireRow.Delete
                    .RemoveItem i
                End If
            End If
        Next i
    End With
    
    Application.ScreenUpdating = True


End Sub