Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

share|improve this question

migrated from stackoverflow.com Jul 17 '11 at 12:07

This question came from our site for professional and enthusiast programmers.

2 Answers 2

* 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).

share|improve this answer
    
I already know how to do it with VBA, but I am specifically asking for a solution without VBA. –  EKet Jul 6 '11 at 2:32

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.

share|improve this answer
    
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. –  EKet Jul 6 '11 at 2:31
    
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 Jul 6 '11 at 6:52
    
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. –  EKet Jul 6 '11 at 19:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.