C
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
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