How to protect an Excel 2010 table & allow to add rows

I need to have an Excel table where cells containing formulas are protected & allow the user to add rows.

when I do sheet protection the table becomes a range. how to avoid that & have both features


I think you have two basic options:

#1 - select the rows in the table where the user may insert new rows and use Format --> Cells and on the [Protection] tab, clear the checkbox next to "Locked" and then protect the worksheet again with the option to allow inserting rows checked.  The entire row(s) must be unlocked for this to work.

#2 - add a command button (preferably from the Forms toolbar) or a shape that is attached to a Macro that will then temporarily unprotect the sheet, insert a new row for the user and then put the sheet back into protected mode.

If you need help with the button/shape with macro setup let us know and we'll give it a go.  We would need to know where you would want to insert the new row(s) at on the sheet.  I suppose those options would be one of these:

a) after a specific row on the sheet (or before a specific row) - this is probably the best option.


b) before or after the currently active/selected cell row.

Since it would be difficult to tell how far down the sheet we could NOT insert new rows, we won't worry about that for the moment, but if there are rows above the possible insertion area where it shouldn't be allowed, then it would be good to know also.


I am free because I know that I alone am morally responsible for everything I do. R.A. Heinlein

7 people were helped by this reply


Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.


Question Info

Last updated May 29, 2020 Views 57,619 Applies to: