Protect worksheet format

  • Thread starter Thread starter Twan Kennis
  • Start date Start date
T

Twan Kennis

How do I protect my Excel worksheet from:
- cell objects being dragged and dropped
- cell objects being copied / cut and pasted from somewhere else

The worksheet is intended to be used as a fill-in form with all possible
protections (only unlocked cells are selectable to edit).
As a pitty this kind of protection is not enough.
By copying and pasting / dragging and dropping unlocked cells all over
the sheet, my formulas get messed up.

Assumely it seems to be unpossible to let users only edit the cell's
VALUE of unlocked cells.
Of course the worksheet needs to be macro-free.

Somebody know how?

With regards,
Twan
 
Your formulas get messed up because they are probably not robust enough.
Why should users not be allowed to copy? They expect to be able to copy and
there is no reason why this should be disabled in an input form.
The real problem is drag&drop (= moving) cells. This can mess up your
formulas if they rely on "normal"cell referencing.
Example:
Your (unlocked) input cells are A1 and A2. Your formula in B1 reads =A1*2
If your user now drags a value from A2 to A1, your formula will result in an
error.

Solution: Use a formula like =OFFSET(B1,0,-1)*2 instead. This will apply
your formula to the cell "one column to the left" (=A1), no matter if the
user moved another cell into A1 or moves the original A1 into another cell.

Cheers,

Joerg Mochikun
 
Back
Top