How do I delete a row in an Excel Table that is on another Sheet that was found with a Userform sear-CodePudding

My first Sub uses a value that I enter in a userform to search a table on another sheet and update the userform with the currently entered values from that sheet.

I have a second Sub that will write the updated values back to the same sheet. I have a checkbox that the user checks when the the related project is complete and we no longer need the data in the row and would like it deleted.

When this is checked and the user clicks update, the sub is to delete that Row from the sheet.

The main sheet is just a replica of the sheet that contains the actual data. That sheet receives updates from Zapier. I have macros that hide rows and columns and this messes with connected apps when they try to add data which is the reason for the second sheet.

I have spent all day today trying to figure out how to delete the row that has been selected by the search function in the userform. The sub performs the same search when updating the row so I thought if I added an IF statement to either update or delete depending on the status of the checkbox.

Here is my code as it stands:

Private Sub UpdateRecord()

     Dim RecordRow As Long
     Dim RecordRange As Range
     Dim Answer As VbMsgBoxResult
     Dim DeleteRow As Long
     Dim DeleteRange As Range
     Dim ws As String
     
      
        ' Find the row in the table that the record is in
        RecordRow = Application.Match(CLng(TextBoxWO.Value), Range("JobSheetData[W/O]"), 0)
        
        ' Set RecordRange to the first cell in the found record
        Set RecordRange = Range("JobSheetData").Cells(1, 1).Offset(RecordRow - 1, 0)
        
        If CheckBoxDelete = "True" Then
        
'---------------------True = Delete-------------------
        
       ' Find the row in the table that the record is in
        DeleteRow = Application.Match(CLng(TextBoxWO.Value), Range("JobSheetData[W/O]"), 0)

        ' Set RecordRange to the first cell in the found record
        Set DeleteRange = Range("JobSheetData").Cells(1, 1).Offset(RecordRow - 1, 0)
        

        Answer = MsgBox("Are you sure you want to PERMANENTLY DELETE this job from the job list?" & vbCrLf & vbCrLf & "This action cannot be undone!", vbOKCancel   vbDefaultButton2, "Confirm removal of job from list")

        If Answer = vbYes Then
        
        ActiveWorkbook.Worksheets("Job List Import").ListObjects("JobSheetData").ListRows(DeleteRange).Delete
        

        End If

'---------------------False = Update-------------------
        
        Else
        
            RecordRange(1, 1).Offset(0, 5).Value = TextBoxHold.Value
            RecordRange(1, 1).Offset(0, 7).Value = TextBoxDays.Value
            RecordRange(1, 1).Offset(0, 9).Value = CheckBoxLocate.Value
            RecordRange(1, 1).Offset(0, 13).Value = TextBoxFirst.Value
            RecordRange(1, 1).Offset(0, 14).Value = TextBoxOveride.Value
            RecordRange(1, 1).Offset(0, 15).Value = CheckBoxBell.Value
            RecordRange(1, 1).Offset(0, 16).Value = CheckBoxGas.Value
            RecordRange(1, 1).Offset(0, 17).Value = CheckBoxHydro.Value
            RecordRange(1, 1).Offset(0, 18).Value = CheckBoxWater.Value
            RecordRange(1, 1).Offset(0, 19).Value = CheckBoxCable.Value
            RecordRange(1, 1).Offset(0, 20).Value = CheckBoxOther1.Value
            RecordRange(1, 1).Offset(0, 21).Value = CheckBoxOther2.Value
            RecordRange(1, 1).Offset(0, 22).Value = CheckBoxOther3.Value
        
        End If
             
End Sub

I also tried to incorporate this code but could not get it to work either:


    Dim Answer As VbMsgBoxResult
    Answer = MsgBox("Are you sure you want to PERMANENTLY DELETE this job from the job list?" & vbCrLf & vbCrLf & "This action cannot be undone!", vbOKCancel   vbDefaultButton2, "Confirm removal of job from list")

        If Answer = vbYes Then
        
        If JobSheetInputForm.ListRows.Count < 1 Then Exit Sub
    
        With ActiveSheet.ListObjects("JobSheet")
        
            JobSheetInputForm.ListRows(CurrentRow).Delete
                        
            If JobSheetInputForm.ListRows.Count > 0 Then
            
                If CurrentRow > JobSheetInputForm.ListRows.Count Then
                
                    CurrentRow = JobSheetInputForm.ListRows.Count
                    
                End If
                
                JobSheetInputForm.ListRows(CurrentRow).Range.Select
            
            Else
            
                CurrentRow = 0
            
            End If
                        
        End With

    End If

I am beat at this point and need your Help! Thank you!

CodePudding user response：

Something like this:

Sub Test()
    
    Dim rngSrch As Range, lo As ListObject, DeleteRow, v
    
    Set lo = ActiveWorkbook.Worksheets("Job List Import").ListObjects("JobSheetData")
    
    Set rngSrch = lo.ListColumns("W/O").DataBodyRange 'range to search in
    v = CLng(TextBoxWO.Value)                         'value to search
    
    DeleteRow = Application.Match(v, rngSrch, 0)      'try to match a row
    If Not IsError(DeleteRow) Then                    'got match?
        If MsgBox("Are you sure you want to PERMANENTLY DELETE this job from the job list?" & _
                   vbCrLf & vbCrLf & "This action cannot be undone!", _
                   vbOKCancel   vbDefaultButton2, "Confirm removal of job from list") = vbOK Then
                   
                   lo.ListRows(DeleteRow).Delete
        
        End If
    Else
        MsgBox "Value not found!" 'no match
    End If

End Sub