Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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"
  'YOUR CODE HERE
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

Your Answer

 
discard

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.