Locking Inserting of Rows at certain rows?

[Joe HM]

Hello -

I was wondering if there is a way I can lock a worksheet so that the
user cannot insert rows between row 1 & 2 and row 2 & 3? Otherwise the
user should have full control over the content of colums 1-5 and row
2-#. He/she should be able to insert, format font and background color
and edit text in those cells.

Is there any way this can be done?

Thanks!
Joe

 

[Guest]

Joe
Protection is a bit of a blunt instrument for this, but you can get the fine
control you want by setting up an array formula. You cannot insert within an
area containing an array, so in your case highlight A1:A3 (or somewhere else
you dont need) and enter '=0' (or any other simple formula) in the formula
bar then press cntrl Alt and enter to enter as an array. You should see
curly brackets around your formula. If so you can now only edit the three
cells together and you cannot insert between them

HTH
cheers
Simon

 

[Earl Kiosterud]

Simon,

That's a clever way to prevent inserting. Some things to consider, though.
Users sometimes select only a part of a full row (just the actual used cells
of the row, for example). So you'd probably want to have the array formula
somewhere in the middle of the used columns -- not at the end. You can hide
the column containing the array formula.

By the way, it's Ctrl - Shift - Enter.

 

[Joe HM]

Hello -

Yeah ... I realize that it is a blunt way and I will probably think of
something else.

Thanks for the reply, though, that's actually pretty slick and good to
know!

Joe