Delete a row but button should only work in specified table-CodePudding

I have a budget template with tables for each budget category. I have buttons for users to add rows to each budget category with the rows in each category being set as a table so that my total functions work no matter how many rows are added where. But Id like users to also be able to delete rows like if they add too many or if roles change during the time the budget is changed, etc. I don't want users to be able to delete rows such as headers or totals. Protecting the rows doesn't work since the row number can change at any time with new rows being added at any time. The delete selected row code is below as well as my add a row code for the first category which is full time employee salary.

delete selected row - not good since can delete important rows

Sub DeleteSelectedRow()

Rows(ActiveCell.Row).Delete

End Sub

add row to category - good since adds rows to table to keep formulas working

Sub AddConsultant()

Dim ws As Worksheet

Dim tbl As ListObject
Set ws = ActiveSheet

Set tbl = ws.ListObjects("Consultants")
tbl.ListRows.Add

End Sub

CodePudding user response：

Try this:

Sub AddConsultant()

    Dim ws As Worksheet
    
    Dim tbl As ListObject
    Set ws = ActiveSheet
    
    Set tbl = ws.ListObjects("Consultants")
    tbl.ListRows.Add

End Sub
Sub DeleteSelectedRow()
    Dim ws As Worksheet
    Dim tbl As Range
    
    Set ws = ActiveSheet
    Set tbl = ws.ListObjects("Consultants").Range
    
    If InRange(ActiveCell, tbl) Then
        Rows(ActiveCell.Row).Delete
    Else
        MsgBox ("Cannot Delete")
    End If
End Sub
Function InRange(Range1 As Range, Range2 As Range) As Boolean
    ' returns True if Range1 is within Range2
    Dim InterSectRange As Range
    Set InterSectRange = Application.Intersect(Range1, Range2)
    InRange = Not InterSectRange Is Nothing
    Set InterSectRange = Nothing
End Function

CodePudding user response：

You can check whether the selection (or part of the selection) range is inside the table of interest.

For example:

Sub AddConsultant()
    AddRow "Consultants"
End Sub
Sub RemoveConsultant()
    RemoveSelectedRow "Consultants"
End Sub

Sub AddSite()
    AddRow "Sites"
End Sub
Sub RemoveSite()
    RemoveSelectedRow "Sites"
End Sub


'methods to add/remove rows from the specified table
Sub AddRow(tableName As String)
    ActiveSheet.ListObjects(tableName).ListRows.Add
End Sub

Sub RemoveSelectedRow(tableName As String)
    Dim rng As Range, ok As Boolean
    
    If TypeName(Selection) = "Range" Then 'is a range selected?
        'is the range in the required table?
        Set rng = Application.Intersect( _
             ActiveSheet.ListObjects(tableName).DataBodyRange, Selection)
    
        If Not rng Is Nothing Then
            rng.EntireRow.Delete
            ok = True
        End If
    End If
    
    'didn't delete anything?
    If Not ok Then MsgBox "First select one or more rows in " & _
                          tableName & " table"
End Sub