Dynamic Protected Range?

[Matt]

I have a sheet with formatted rows, and each new row with consistent
formatting is added using a macro. I'd like to protect the sheet but
allow the user to edit certain ranges, i.e. the formatted rows. Since
the range that the rows exist changes every time a new row is added,
the user-editable range needs to also change. Is this possible?

 

[JLatham]

Some code similar to this might help you. But you'd probably want to use it
in the Worksheet_Change() event so that it only actually does something when
the number of rows in your dynamic area changes. How you decide on that is
usually the most complex part of doing something like this.

Sub DynamicUnlocking()
'assumes that the range you want
'to unlock is in column D and
'starts at row 2, you just don't
'know where it ends, it needs to
'end at the last used cell in
'column D
'assumes worksheet is active
Dim unlockRange As Range
Set unlockRange = Range("D2:" & _
Range("D" & Rows.Count).End(xlUp).Address)
ActiveSheet.Unprotect
' may need to use this if sheet has password
'change "sheetpw" to actual password
'ActiveSheet.Unprotect Password:="sheetpw"
'this next command is going to lock ALL
'cells on the sheet
Cells.Locked = True
'now just unlock the ones we
'want unlocked
unlockRange.Locked = False
'put sheet back into protected state
'in order to make your protection scheme work
ActiveSheet.Protect
' may need to use this if sheet has password
'change "sheetpw" to actual password
'ActiveSheet.Protect Password:="sheetpw"

End Sub