Cut is unsafe - call for solutions

[cjakeman]

Been using (and programming) Excel for some years but only dawned on
me recently that Edit:Cut can corrupt your references as it doesn't
actually cut and paste but moves the cut cells just like "drag and
drop" does.

It seems this comes up in the newsgroup a few times every year (from
at least 1998). There's no mention of the issue at http://blogs.msdn.com/excel
so it's unlikely to be fixed in the new Excel 2007.

More scanning of this newsgroup comes up with several workarounds.

Several posters suggest using VBA to disable the drag and drop along
with the Cut, Copy and Paste menu options and keyboard shortcuts.
There is code for this from July 2001 at http://www.j-walk.com/ss/excel/eee/eee020.txt

Stephen Bullen, in his book "Professional Excel Development", offers
some simple VBA to deliver a safe version of Cut, Copy and Paste. Drag
and drop still has to be disabled though.

Lastly, EarlK (Jan 2003) suggests using indirect referencing instead
of direct, e.g. =SUM(INDIRECT("A2:A5")) This solution doesn't need any
VBA (which the user can always bypass anyway).

Is this a fair summary? Have I missed anything?

Chris Jakeman

 

[NickHK]

Chris,
Not sure exactly what you problem is. Care to clarify.
Actually the 2 action are not the same in terms of what Excel does. Add some
suitable debug code to _Change and _SelectionChange events and you see:

'Cut-Paste
WS_SelChange
WS_Change
WS_Change

'Drag-drop
WS_Change
WS_Change
WS_SelChange

Using these sequences, along with .CutCopyMode you can determine which
action is being performed.

NickHK

 

[cjakeman]

Chris,
Not sure exactly what you problem is. Care to clarify.

If I provide a spreadsheet for others to use where everything is
locked and protected except an area for the user to work in, the user
can accidentally change the references in my formulas. This can happen
even though my formulas are locked and protected.
Imagine 2 rows with a formula in the first column. E.g.

=B1/C1, 6, 7
=B2/C2. 3, 4

Unlock the cells containing 6,7,3,4 and protect the sheet (and
therefore the formulas)
Then drag the 6 and drop it on the 3 (or use cut and paste).
The formula =B1/C1 becomes =B2/C1 and the other formula becomes =#REF!/
C2

Your users will blame you and your "buggy" spreadsheet, not Microsoft
and Excel.
I'm thinking Stephen's solution is best but what do other people do?

Actually the 2 action are not the same in terms of what Excel does. Add some
suitable debug code to _Change and _SelectionChange events and you see:

Using these sequences, along with .CutCopyMode you can determine which
action is being performed.

That's brilliant, thanks. I can imagine lots of cases where that would
be useful.

Chris Jakeman