Jim Rech wrote...
Good thought, Harlan. That works with a copy. A cut and paste
or a drag and drop seems to get around it though.
....
OP seemed pretty explicit about _copying_, and implied the worksheet
was protected. And it did seem like you were limiting your original
response to disabling copy operations. But you raise an interesting
generalization.
First off, Excel prevents 'move' operations from range to range within
protected worksheets. Next, it's impossible to drag & drop between
worksheets. That leaves cut & paste between worksheets.
This is where Excel is REALLY STUPID. Excel 2002, 2000 and 97 all allow
pasting ranges cut from other worksheets into unlocked ranges in
protected worksheets. Maybe this has been fixed in Excel 2003, but I
doubt it.
The good news is that if I make a slight modification to my Change
event handler, specifically, changing
Target.Formula = cf
to
If Not Target.Locked Then Target.Formula = cf
this prevents the 'move' operation as well as avoiding the apparently
untrappable runtime error when Excel believes Target is locked. Target
*becomes* locked after Application.Undo following *move* operations
even if both source and destination cells are unlocked whether the
worksheet is protected or not. In this case, this looks like a feature
since it can be used to prevent the interworksheet move operation even
though it won't retain the pasted values.
So, while my Change event handler won't retain values moved into cells
in protected worksheets, it does appear to prevent worksheet/workbook
corruption that Excel's apparently much weaker than previously believed
worksheet 'protection' fails to prevent.
Amazing what you can discover about Excel. What a piece of . . .
