Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

My goal is to protect a sheet, but make users able to edit and add rows to the contained tables.

When I try to protect the sheet with no cells locked, the tables become unable to add rows even though their cells are not locked. I have tried to check all the Allow all the users of this worksheet to checkboxes, but it does not help me.

Does anybody know of a work around or will I need to create an Insert Row button with associated VBA to make this work?

share|improve this question
up vote 0 down vote accepted

You can use a macro to make changes on locked worksheets. For example:

Sub MyMacro()
Sheet1.Unprotect Password:="Secret"
Sheet1.Protect Password:="Secret"
End Sub

Please read this article for more about running macros on protected worksheets.

If you need help on writing the macro, you can ask for help on our sister site StackOverflow.

share|improve this answer
This has the problem of if the code breaks or the user interrupts the code, the sheet will be left unlocked. If you use this approach it is probably best to use an on error to either force your code to continue running (resume next) or upon an error, stop running the code and just jump to the end of the code, and re-protect. – Jarvin Jun 28 '10 at 15:35

When I click 'Protect Sheet...' I get a number of options for what I want to allow users to do, including 'Insert Rows.'

Am I misunderstanding your question or are you not seeing that?

share|improve this answer
I see the same options as you, but checking "Insert Rows" doesn't help - the table still refuses to add rows. – Christian Madsen Jun 29 '10 at 10:41
In this instance, "Insert rows" seems to refer to adding empty rows to the spread sheet (using right click -> insert, for example), not writing something in an already existing row. – jarlemag Jul 27 '15 at 15:05

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.