Inserting new rows without inheriting protection from row above/be

[Colleen]

Hello
I am hoping that someone can help me out with this. We have a spreadsheet
that we want others to add additional rows to, our top level and below level
row has cells that have protection on them. When we try to add additional
blank rows the protection is inherited which we don't want. Can anyone help?
Thanks

 

[Paul]

John,

I guess that you are using XL 2003, 2007 with a protection setting which
allows row insertion.

If all the cells in the row above the insertion row are locked, the default
setting, then all the cells
in the new row are locked too. If some of the cells are unlocked, then the
cells directly below in
the insertion row will be editable. However, you would be able to change
the data in all the previous
rows where the cells have this setting; which may defeat the point of
protecting in the first place.

You could do it with a vba macro, but the result would be a messy mix of
editable rows and protected
ones. The on-insertion macro could lock all existing worksheet cells before
the insertion is done. But what
if you want to go back and edit a previously inserted row?....

A simpler macro solution would be to allow certain users to unprotect the
sheet on opening the workbook and
protection being automatically applied on closure. That, though, isn't
fool-proof.

Regards
Paul

 

[Shane Devenshire]

Hi,

You will need to control where the new unprotected rows are going to be
added, because Excel passes the protection from the cells above down to the
new row. So to insert an unprotected row 3 row 2 must be unprotected.

If rows 1 and 2 are protected and you want to let the user insert a row
below those that is unprotected, then unprotect row 3 and hide it. When the
user selects row 4 (first visible row below 2) and inserts a new row that row
will be unprotected.