Turning off Drag and Drop automatically

I have an input spreadsheet that I distribute to users. I have used sheet protection to ensure users can only change input areas. The problem is users are still able to drag and drop within the input areas, which causes #REF! errors. Is there a way to stop users dragging and dropping?

By Neale Blackwood

This solution works on Excel 97 and later versions. In the Tools menu under Options, in the Edit tab there is a check box to “Allow cell drag and drop”. If you uncheck that option it will stop the user from using drag and drop. You can use a macro to turn this option off when you open the file and another macro to turn it back on when the file is closed. To insert the macros, right click the Excel symbol to the left of the File menu and select View Code (bottom of list) and then enter the following two macros in the blank area:

Private Sub Workbook_Open()
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CellDragAndDrop = True
End Sub

Users will still be able to cut and paste. The user will not see the arrow-shaped mouse cursor that is used for drag and drop operations. These macros will not stop users from manually turning the drag and drop option back on. This macro does affect the user’s Excel environment. After the user has opened the file the user can’t drag and drop in any open workbook. This may be confusing to some users. This constraint can usually be minimised by instructing users to: open the input file, make the necessary changes and then save the close the file – do not leave the file open while working on other files unless necessary for the entry of data into the input file.

Extras

This requires that the user turns on the macros when he opens the file.
In Excel 2007 the way this happens has changed so that the user can open the the file and use it and not realise there were macros to turn on. There is a section above the formula bar that displays when there are macros in the file and asks whether to enable them. Some users do not notice this.