Angus,
You are so right. Cell drag and drop is bad news at data entry time, and
should never be used with or without the protection mechanism. It's for
design time only, wherever there are formulas at play. There seems to be no
protection that will prevent this. I've always told clients either to not
tell data entry folks about it, or to tell them not to use it. If it's
inadvertent clobbering of formulas via drag and drop, you could prevent it
manually with Tools - Options - Edit - Allow dell drag and drop (turn off),
or automatically with this in the ThisWorkbook module:
Private Sub Workbook_Open()
Application.CellDragAndDrop = False
End Sub
This won't stop deliberate formula damage from the determined hackers --
they can just turn it back on manually. Also, it's an application-wide
setting -- it will affect other sheets in the workbook, and other open
workbooks (Excel doesn't handle multiple open workbooks well). It's not
stored with workbooks when saved, like the dopey calculation auto/manual
setting. You could turn it back on automatically when the workbook is
closed with:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CellDragAndDrop = True
End Sub
If there are to be multiple workbooks open concurrently, you could instead
do this in WindowActivate and WindowDeactivate subs, but this presumes all
the other workbooks have the CellDragAndDrop turned on. Otherwise, you'll
mess them up. It seems endless.
All this applies to Excel 2002 and prior. I don't know if it's been
addressed in a later version. I kind of doubt it, but you could look into
it. Or maybe someone else will come forward with a solution. I'm going to
get another cup of coffee. Ranting consumes energy.
