Drag and drop cells-turn it off?

[Ranger_j]

I'm having trouble with a spreadsheet, in that users have a nasty habit of putting data into the wrong cells, then dragging them where the data is supposed to go, which, of course, drags the formulas, messes up calcuations elsewhere on the sheet, and creates mass chaos.

I know I can turn off drag and drop in the tools menu, but that seems to turn it off in my Excel executable, not tie the command to the particular spreadsheet. So problem not solved if someone opens the sheet on another computer.

Is there a way to turn off drag and drop in a workbook (or even an individual sheet) and have that setting follow the file wherever it may go?

 

[dave3009]

Hi

How about protecting the cells which you don't want to allow the user to change

KR


Dave

 

[VoG]

Try this. Right click the Excel logo just to the left of File on the memu bar, select View Code and paste in

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

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

 

[Ranger_j]

That seems to work. It applies the option globally, but that's good enough for me.

Thanks!

 

[dave3009]

You could try VoG II's code in an activate/deactivate event also this way you could have the drag and drop active for other workbooks

 

[VoG]

Dave is correct. You could remove the code from the ThisWorkbook module then right click the sheet tab, View Code and paste in

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

Private Sub Worksheet_Deactivate()
Application.CellDragAndDrop = True
End Sub