up vote 2 down vote favorite
1
share [g+] share [fb]

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?

Thanks from Denmark. Christian

link|improve this question
50% accept rate
feedback

2 Answers

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.

link|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. – Dan Jun 28 '10 at 15:35
feedback
up vote 0 down vote

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?

link|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
feedback

Your Answer

 
or
required, but never shown
discard

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