It seems that the formaula in a protected cell can be altered by doing a
drag-and-drop of one of the protected cells formula precedent cells.

Can this be stopped?

The first thing I would check is this. Make sure that the cells you
want to protect are formatted at "Locked". Protection only works on
Locked cells.

(Select Cell) Format Menu Cells Protection

The first thing I would check is this. Make sure that the cells you
want to protect are formatted at "Locked". Protection only works on
Locked cells.

(Select Cell) Format Menu Cells Protection

The cell I wish to protect is definately locked and the sheet is protected.

"kfogle" wrote:

The first thing I would check is this. Make sure that the cells you
want to protect are formatted at "Locked". Protection only works on
Locked cells.

(Select Cell) Format Menu Cells Protection

First, make sure that all cells on the sheet EXCEPT where you want to accept
data entry from the user are Locked. This will keep them from being able to
drag a precedent cell into another (Locked) cell.

If a user drags a precedent cell into an empty unlocked cell, the formula in
the Locked cell does indeed change to show the new reference. But the
formula itself has not changed, just the references to the precedent data,
which the user has just redefined.

Other conditions create other results. Consider this layout: Cells A1:B2
are unlocked, Cell C1 = A1+B1, cell C2 = A2 + B2 and are locked. Cell A3 is
unlocked, but not a precedent for anything, just unlocked and empty. The
sheet is protected.

Trying to drag C1 or C2 to any location results in a "cell you are
attempting ... locked" and you are prevented from doing that.

You can drag any ONE of A1:B2 into A3 and the associated formula will be
altered to reflect the move. i.e., you move B1 into A3, C1 changes from
=A1+B1 to =A1+A3 at this point you could even move B2 up to B1 (left empty by
the previous move) and the formula in C2 would become =A2+B1, again an
alteration reflecting the user directed relocation of precedent data.

However, there are some things that wouldn't work. Go back to the original
setup where C1=A1+B1 and C2 = A2+B2 and try dragging A1 into A2. You'll get
a "replace contents?" prompt, and if you continue and say YES, then the
formula in C2 suddenly changes to REF# because you've deleted the previous
content of A2.

I suppose in one fashion this could be looked at as having changed the
formula, but it does follow the general scheme of the way Excel works when
moving references around. Your first line of defense to prevent this is to
only unlock cells where the input comes from an 'outside' source, such as the
user and must be unlocked to provide functionality.

I don't see this as being a huge problem. If you move it accidentally
during design and tell the user to enter something specific in A1 (having
accidentally dragged that to A2) and nothing happens: well, you should have
caught that during final testing before release. If the user makes such a
change, woe be unto them. In over a decade of providing Excel solutions
professionally, I don't believe I can recall a single instance of where an
end user did such a thing - at least not and then contact me asking "what
happened - your application turned to crud just out of the blue on me!".

"Angus" wrote:

The cell I wish to protect is definately locked and the sheet is protected.

"kfogle" wrote:

The first thing I would check is this. Make sure that the cells you
want to protect are formatted at "Locked". Protection only works on
Locked cells.

(Select Cell) Format Menu Cells Protection

See my other comment - his problem seems to not be with the cell with the
formula in it, but rather with unlocked cells that are referenced by the
formula in the Locked cell.

Angus: I don't know of a way to stop that behavior. While the situations I
described there will generally protect against total destruction of a formula
except by a determined end user, there is still the possibility that a
precendent cell could get dragged and dropped into another unlocked cell that
is not referenced anywhere else. Example of one might be where you have an
unlocked cell for user to enter their name or a street address or similar
information.

"kfogle" wrote:

The first thing I would check is this. Make sure that the cells you
want to protect are formatted at "Locked". Protection only works on
Locked cells.

(Select Cell) Format Menu Cells Protection