Protecting formulas in table while permitting new rows / deletion of rows

Question

I am using some tables that are designed for easy data entry by users. Some of the columns are formulas. I would like to protect the columns that use formulas, but still permit users to add new rows and delete existing rows from the table.

There is some very helpful code here: https://answers.microsoft.com/en-us/msoffice/forum/all/inserting-deleting-rows-or-columns-in-a-protected/17303e19-dabe-4f38-9250-2ef213f1e13d

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Dim lstObj As ListObject
    Dim rngTable As Range
    Dim rngOffsetTbl As Range
   
    If Target.Cells.Count > 1 Then Exit Sub 'If more cells than one changed (as in copy paste)
   
    If Target.Value = "" Then Exit Sub  'If value deleted
   
    On Error GoTo ReEnableEvents
    Application.EnableEvents = False
   
    'Assign the table to a ListObject variable
    'Me is the generic object for the worksheet to which this module belongs
    Set lstObj = Me.ListObjects("Table2")   'Edit "Table2" to your table name
   
    Set rngTable = lstObj.Range 'Assign the table range to a range variable
   
    With rngTable
        'Assign the next row under the table to a range variable
        Set rngOffsetTbl = .Rows(.Rows.Count + 1)
    End With
   
    'Test if the changed cell is in the next row after the table
    'Not nothing then is something so target is in the next row under the table
    If Not Intersect(Target, rngOffsetTbl) Is Nothing Then
        'Me is the generic object for the worksheet to which this module belongs
        Me.Unprotect Password:=strPass
       
        With rngTable
            'Resize rngTable to the new size required for the table (eg. Rows count + 1)
            Set rngTable = .Resize(.Rows.Count + 1, .Columns.Count)
        End With

        lstObj.Resize rngTable  'Resize the table to the new range size
           
        Me.Protect Password:=strPass
    End If
   
ReEnableEvents:
   
        If Err.Number <> 0 Then
            MsgBox "An error occurred in module " & Me.Name & " Private Sub Worksheet_Change"
        End If
       
        Application.EnableEvents = True

End Sub

This allows users to add new rows (great), but does not allow users to delete existing rows. Is there a way to do so?

A second question: does the protection applied at the end of the above code inherit the previous protect settings used on the sheet and, if not, is there a way to either do that or, failing that, can someone point me in the right direction to use VBA to apply specific protect settings?

Answer 1

1. Issue

If a column is locked and the worksheet is protected you cannot delete entire rows without removing the protection first.

So the workaround would be to add a button Delete selected row (or a keyboard shortcut to a procedure) and write a code that unprotects the worksheet, deletes the row and protects it again.

Alternative to unprotect/protect
Do some research on how to use the UserInterfaceOnly parameter properly in combination with the Workbook_Open event, this allows to protect only the user interface but VBA still can access the worksheet without restrictions. With this you will still need a dedicated button (or shortcut) to delete a row but you don't need to unprotect/protect everytime.

2. Issue

You just need to read the official documentation of the Worksheet.Protect method. There you see that every parameter (besides the Password parameter) has a default to True or False. That is what is used in case you don't specify a parameter. It does not use anything that was set perviosly.