Delete Button on UserForm won't Cooperate (Run-time error ‘1004’, Range Class Failed)

[rorobear]

Hello Everyone,

Hoping someone can assist with this small problem. My delete button on userform continues to fail (Run-time error ‘1004’ Delete method of Range class failed) and I can’t seem to figure out why. Any assistance would be greatly appreciated. The workbook is attached.



Private Sub DeleteSelection_Click()
Dim lngListBoxIndex As Long
Dim strRecName As String
If Me.ComboBox1.Value = "" Then
MsgBox "Select a worksheet from the dropdown", vbInformation, "Select Worksheet"
Exit Sub
End If
With Me.ListBox1
For lngListBoxIndex = 0 To .ListCount - 1
If .Selected(lngListBoxIndex) = True Then
strRecName = .List(.ListIndex, 0)
If MsgBox("Are you sure you want to delete the record?", vbQuestion + vbYesNo, "Delete Record") = vbYes Then
Application.ScreenUpdating = False
Sheets(ComboBox1.Value).Rows(lngListBoxIndex + 2).EntireRow.Delete '->modified code in order for it to delete based on the item selection in the ComboBox1
Application.ScreenUpdating = True
MsgBox "Record has now been deleted", vbInformation, "Delete Record"
End If
Exit For
End If
Next lngListBoxIndex
End With
Call combobox1_change '-> added code in order for the ListBox to updated after the row is deleted in.
End Sub

[georgiboy]

Perhaps you could change this row:

Sheets(ComboBox1.Value).Rows(lngListBoxIndex + 2).EntireRow.Delete
To:

Sheets(ComboBox1.Value).ListObjects(1).DataBodyRange(lngListBoxIndex + 1, 1).EntireRow.Delete

[rorobear]

georgiboy,

i knew it was an easy fix, now i just need to figure out how to add new entries. thank so very you for the speedy reply.