On Wednesday, December 8, 2010 9:44:55 AM UTC-8, Cornelius wrote:
Hi guys,

i know there are a lot of threads about this topic on the net. Most of
them provide solutions using macros in order to manually insert rows
on executing. Desipite the efforts that people put into their
solutions (and there are nice once), none of them really do convince
me, because they all lack usability.

A short introduction to the problem:
Michael Weinhardt posted a thread that is spread over many forums with
the title "Can't insert row in data table in protected worksheet" that
describes best, what I am (and I guess many others) searching for.
Unfortunately there are no solutions available.
I have a data table, where I have columns that include formulas that
need to be protected in order to prevent accidental changes. The
worksheet is protected in a way that rows can be inserted and deleted.
Of course this does not actually work well, as there are cells
included in every row that are protected.

The problems:

1. If I insert a new row (somewhere in the middle of the table) a
message-box pops up telling me that "the cell or chart you are trying
to change is protected and therefore read-only". I don't really
understand, why this has to be, but I can't have it for productive
use.

2. If I want to insert a new row at the end of the table (e.g. going
to the last cell of the table and press TAB), a new cell isn't entered
automatically (but it is, if I unprotect the sheet).

3. If I want to delete a row it is not possible, as there a protected
cells within the row (although the function was activated when
protecting the worksheet)

As I am concerned about the usability of the table a macro that needs
to be invoked manually is not a solution that I can accept. Users
should be able to work as they usually do. Actually I personally
believe that what I want is not that unusual or caused by exceptional
perceptions on what should be possible.
If you for example unprotect a cell that includes a formula, you will
notice a exclamation mark next to the cell, telling you that this cell
contains a formula, but is not protected. But as long as you don't
protect the worksheet, the cell-protection itself is useless. But if
you protect the worksheet, you are not able to insert/ delete rows
that way you would expect it to. I more and more believe that this is
simply caused by bad software-design and not because I want to do
something very unusual.

The only solution that I came up with, is to unprotect the sheet and
rather create a macro that checks the important formulas when invoking
the Workbook_BeforeClose-Event. If these have been altered the user
gets a notice, whether the change was on purpose or if the old
forumula should be restored.

I am thankfull for any advice you share on how you handled this
problem.
Kind regards,

Cornelius

Hello
I have exactly the same problem as you did. the database needs to be locked to some users, but these users need to enter data. I can let them insert a row, but they cannot enter data in it! Have you found the solution to your problem? I`ll be thankful if you can help me in this