Prevent a user from adding or removing row s of a Range but allow him to do so on another Range on the same sheet

Question

Two specified ranges on my sheet are Titles (A11:O15) and Contents (A18:O30).

I'm trying to find a solution to stop a user from adding or removing any rows of titles, but he must still be able to change the content of those titles.

Contrarily, the user has complete freedom to alter the contents.

How can I make my sheet's first 17 lines editable while preventing the insertion or deletion of rows before row 17?

Answer 1

The only thing I can offer is a workaround based on https://stackoverflow.com/a/7479837/17017616

To prevent users from adding/removing rows and/or columns to certain cells, you must first create a named range for those cells. The range in my example has the name protected Area and spans 32 total cells.

You then insert this code into the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    Const referenceCellCount = 32
    Static recursionGuard As Boolean
    Dim rngProt As Range
    
    
    If recursionGuard = True Then
        Exit Sub
    End If
    
    Set rngProt = ThisWorkbook.Names("protected_Area").RefersToRange
  
   ' Adding or removing Rows in the protected area will
   ' change the size of the range and thus the total count of cells
    If referenceCellCount = rngProt.Cells.Count Then
        Exit Sub
    End If
    
    recursionGuard = True
    Application.Undo
    recursionGuard = False
    MsgBox "Foo must not..."

End Sub

Make sure that referenceCellCount matches your case.