Allow insert row in to range with locked cells

[Darren Guess]

Hi All

I have been trying to protect some important cells, among other things, with my excel today. Having found a fair few posts on here I have managed to protect all of the ones in want. For example columns B, C, D, E, G, H & J, Rows 2 to 20 as some contain data and some formulas. Cloumn F and I can now be edited by the user.

The issue I have now is that I want the users of the excel to be able to copy a row that has the formulas set up ready (and is unprotected) in to anywhere they wish between rows 2 to 20 and to be able to update as required.

Have I missed a simple issue (I do have the Insert Rows checkedin the protect WB), or will excel not allow an insert where a row already has a protected cell? Is it going to be a case of adding some VB code to override the protection when inserting the copied row?

Many Thanks in advance
Darren

 

[Darren Guess]

Hi All

I have spent another couple of hours this morning scouring this forum to see if I have set my protection wrong somewhere and that is why I can't insert rows after protecting cells, but all seems to be ok. The procedure I have followed is:

Select all cells in worksheet
Right click, format cells, protection, unselect locked in the options - (all cells should now be unlocked)?
Select all cells that require protection, using Ctrl key to select seperate ranges
Right click, format cells, protection, Select locked in the options - (all cells selected should now have protection applied)?
Toolbar ribbon, Review, Protect sheet - options selected are: Select Unlocked Cells, Insert Columns, Insert Rows

Am I right in thinking that this should now protect my users from accidently wiping the data out of the protected cells (cell positions as mentioned in my first post), but still allow them to insert rows as required?

I am working on Excel 2007, and when I copy and insert a row within the range of protected cells the copied row is forced to the bottom of the protected cells. E.g. There are cells from column B to J rows 2 to 20 that are protected, now if I try and copy and insert a row at say row 5, it is forced to be inserted at the next free row, row 21.

In a couple of other posts I have noticed that other users appear to have the same issue and in the response the user notes that if the tick options in the protect sheet are selected correctly all should work okay, but it doesn't appear to?

If someone could explain a workaround it would be much appreciated.

Darren