1
Is there any way to disable drag and drop of cells on all sheets without a script. I know you can do it globally, there is a setting in the Excel program that disables it locally on your machine. (TOOLS, OPTIONS, EDIT, checkbox ALLOW DRAG AND DROP) But if someone else were to take this file from you and their machine did not have it disabled they could potentially move cells and formulas around via drag and drop.
Thank you!
3
* This is with **VBA (there isn't a way I am aware without)
Under thisWorkbook in the VBA Editor (Alt + F11), in the editor add the following code
Private Sub Workbook_Open()
Application.CellDragAndDrop = False
End Sub
This will turn it off as soon as they open the workbook in question. The only downside is that they will have it turned off on all workbooks until they change it back. Also they can turn it back on after it has been opened by going the route you listed above. The other option is to run a procedure every time they move around the workbook (tedious solution though).
lionz
Posted 2011-07-05T16:50:39.493
Reputation: 131
1
Protect the sheet.
Tools > Protection > Protect Sheet...
Of course, with default options, this also prevents the user from doing other things than just drag and drop and may be overkill in your particular case. But depending on what you want to do exactly, you may find a set of protection options that suits your needs.
Jean-François Corbett
Posted 2011-07-05T16:50:39.493
Reputation: 2 219
this actually doesn't have anything to do with drag and drop. You can still enable drag and drop even if the sheet is protected. – None – 2011-07-06T02:31:40.493
Sure, you can still "enable" drag and drop, but you can't actually drag and drop the protected/locked cells. So this has something to do with preventing drag and drop. Is this a good solution in your particular case? I don't know. That's why I wrote that caveat. – Jean-François Corbett – 2011-07-06T06:52:43.243
I misunderstood, my apologies. But no I do not wish to lock cells, they are supposed to be editable, which is why preventing drag and drop is critical because some numbers can get misplaced and no one would know. It's a very basic feature which MS conveniently attached to only the local machine install instead of the Sheet level. Thanks for the idea though. – None – 2011-07-06T19:24:42.250
I already know how to do it with VBA, but I am specifically asking for a solution without VBA. – None – 2011-07-06T02:32:30.170