Inserted Row NOT to Inherit Lock Property

Hey Guys,

Here's the Deal:

I have a Protected Worksheet with a locked dynamic Range (for the purpose of understanding let's say: A1:E100). I have allow in the protection properties to insert and delete new rows. So, if I want to insert a row between A10 and A11 it will let me. However, if I want to add content to this row it will not let me because it inherited the properties from the Cells Above.

If I inserted a new row in cell A250 I won'r have a problem of course, because this part of the sheet is not locked. Obviously I want to insert the Row between the mentioned dynamic define range (A1:E100).

Is there a way to insert new rows without inheriting the properties from the cell Above?
Is there a way that the new inserted rows aren't locked?

I want to keep my locked Range as it is right now and I don't to insert the row if some other Range that is not between my defined Range.

Thanks a lot for your help in advance.

I don't know of a way to insert a row without inheriting from above. You can have code do this; unprotect, insert, unlock the new row, and reprotect.

Please Login or Register  to view this content.

Thank you!

Yes, code will solve the problem but I wanted to solve it using only the properties of the sheet. So that when the user will right-click > insert row it will create a row without inheriting the above cells properties. I don't want to use Events either so I can I will have to insert a Button that does this.

Thanks for answering

When you insert a row, Options > Advanced, Cut, copy, and paste: tick Show Paste Options button. Then you'll have the choice of copying formatting from above, below, or clearing formats.

Originally Posted by amartinez988

Thank you!

Yes, code will solve the problem but I wanted to solve it using only the properties of the sheet. So that when the user will right-click > insert row it will create a row without inheriting the above cells properties. I don't want to use Events either so I can I will have to insert a Button that does this.

Thanks for answering

You realize this is the Excel Programming / VBA / Macros forum?

I wonder if there is a registry setting that changes the default copyorigin. AF, do you know?

shg, I was wondering the same

Originally Posted by shg

I wonder if there is a registry setting that changes the default copyorigin. AF, do you know?

I couldn't find a registry key for that. It doesn't mean that's the final word; only that I didn't find it if it does exist.

An alternative kludge is if you always insert at say row 11, format row 10 the way you want (unlocked) and hide it.

AlphaFrog, that's not a bad solution ...