1

My sheet contains 2 named ranges Titles (A11:O15) and Contents (A18:O30).

I'm looking for a way to prevent a user to add or remove any row(s) of Titles, but he must be able to edit its content.

On the other hand Contents can be modified without any restriction by the user.

How can I protect the first 17 lines of my sheet so that they can be edited but prevent the addition or removal of rows before the row 17 ?

Xodarap
  • 343
  • 1
  • 6
  • 23
  • So "*I'm looking for a way to prevent a user to add or remove any row(s) of Headers, but he must be able to edit its content.*" means able to edit the contents of the headers? – Solar Mike Feb 20 '23 at 14:10
  • Yes, user can't add or remove rows from this range but he can edit the content of the already existing rows – Xodarap Feb 20 '23 at 14:13

1 Answers1

2

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

First you create a named Range for the the cells you do not want the user add/remove rows and/or columns. In my example the name of that range is protected_Area and it spreads over 32 cells in total.

Next you add this code to 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.

leosch
  • 451
  • 2
  • 10
  • yeah, I'm working on something similar also using Application.Undo. Seams to be the only solution – Xodarap Feb 20 '23 at 15:38
  • Please mind that the answer does not yet cover the case where the user deletes many rows, so that the `protected_Area` is "empty" and cannot be converted to a range (#REF error). – leosch Feb 20 '23 at 15:43