VBA Userforms and Deleting a Row in the spreadsheet

[ScottKoll]

Hi Guys,
I'm having a problem deleting Rows in the spreadsheet from a Command Button titled "Remove". In short, I want to select an item from a list box within a userform and then click the "Remove" button to delete that row. Unfortunately after executing the command, "Run-time error '1004': Application-defined or object-defined error" keeps coming up. It's funny because I'm not even running the macro where the error is occurring. The Run-time error highlights "Set JobFind = .Find(ProjectListBox.Value)" under the ProjectListBox_Click() macro.

'Private Sub RetentionRemove_Click()
' ProjectIDFind = Range("RetentionIDs").Find(What:=RetentionProjectID.Value).Row
' Dim DeleteProjectMessage As String
' DeleteProjectMessage = "Are you sure you want to delete " + RetentionProjectName.Text + "?"
' If MsgBox(DeleteProjectMessage, vbQuestion + vbYesNo, "Confirm Delete") = vbYes Then
' Range("A" & ProjectIDFind, "J" & ProjectIDFind).Delete Shift:=xlUp
' End If
'End Sub

'Private Sub RetentionUpdate_Click()
'JobFind = Range("RetentionIDs").Find(What:=RetentionProjectID.Value).Row
' Cells(JobFind, 2).Value = RetentionProjectID
' Cells(JobFind, 3).Value = RetentionProjectName
' Cells(JobFind, 4).Value = RetentionPCID
' Cells(JobFind, 5).Value = RetentionPCName
' Cells(JobFind, 6).Value = RetentionJVFlag
' Cells(JobFind, 7).Value = RetentionARRetention
' Cells(JobFind, 8).Value = RetentionAPRetention
' Cells(JobFind, 9).Value = Format(ARRetCollectiondate, "mm/dd/yy")
' Cells(JobFind, 10).Value = Format(APRetCollectionDate, "mm/dd/yy")
' With Range("G" & JobFind, "H" & JobFind)
' .NumberFormat = "#%"
' .Value = .Value2
' End With
'End Sub

'Private Sub ProjectListBox_Click()
'Dim JobFind As Range
'With Range("RetentionNames")
' Set JobFind = .Find(ProjectListBox.Value)
' If JobFind Is Nothing Then
' MsgBox ("Project Job Number not Found!")
' RetentionProjectID.Value = ""
' Else
' With Range(JobFind.Address)
' RetentionProjectID = .Offset(0, 1)
' RetentionProjectName = .Offset(0, 2)
' RetentionPCID = .Offset(0, 3)
' RetentionPCName = .Offset(0, 4)
' RetentionJVFlag = .Offset(0, 5)
' RetentionARRetention = .Offset(0, 6)
' RetentionAPRetention = .Offset(0, 7)
' ARRetCollectiondate = .Offset(0, 8)
' APRetCollectionDate = .Offset(0, 9)
' End With
' End If
'End With
'End Sub

 

[dmt32]

Hi,
welcome to the board

See if this update to your code helps:

Private Sub RetentionRemove_Click()
    Dim DeleteProjectMessage As String, Search As String
    Dim Result As VbMsgBoxResult
    Dim ProjectIDFind As Range
    
    Search = RetentionProjectID.Value
    
    DeleteProjectMessage = "Are you sure you want to delete " + Search + "?"
    
    Set ProjectIDFind = Range("RetentionIDs").Find(What:=Search, LookIn:=xlValues, lookat:=xlWhole)
    If Not ProjectIDFind Is Nothing Then
    
        Result = MsgBox(DeleteProjectMessage, vbQuestion + vbYesNo, "Confirm Delete")
        If Result = vbYes Then ProjectIDFind.EntireRow.Delete Shift:=xlUp
        
    Else
        MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
    End If
End Sub

Dave