Disable Drag and Drop???

[Parrow]

How can I, or can I, disable Cell Drag and Drop via code or settings tied to the workbook?

I see there is an option I can un-check in the settings for Excel, (Enable Fill Handle and Drag and Drop) but the sheet I am making will be dropped onto 50+ computers, I would rather just lock up the workbook as much as possible, vs. having to manage settings on everyones comptuer.

Thanks.

 

[Smitty]

Welcome to the Board!

You can use something like this:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()<br>    Application.CellDragAndDrop = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

You might also want to look into worksheet/book protection.

HTH,

 

[Parrow]

Awesome, thanks.

I use worksheet and workbook protection, but they can still drag and drop cells around the sheet. I will try that code and see if it does what I am wanting it to do. Thanks.

 

[Parrow]

Awesome! I love simple fixes. Just used the suggestion.

This has been the bane of my existence for months now. lol. Sheeple just won't do what is instructed and prefer to drag names around instead of deleting and typing...which in turn ruined the forumulas I had in the cell.

This little bit of coding just eliminated that from ever happening again.

Thanks a bunch.

 

[CatyH]

How do i get my drag/drop back in all the rest of my workbooks now? it appears to be disabled everywhere. THanks!

 

[Smitty]

If you used the code method above, just set it back to True. As for it happening in all of your workbooks, where did you put the code? If it's in Personal.xlsb, you probably want to get it out of there.

 

[CatyH]

If you used the code method above, just set it back to True. As for it happening in all of your workbooks, where did you put the code? If it's in Personal.xlsb, you probably want to get it out of there.

Well - I had tried it in a sheet and in a module. I think it was supposed to go into ThisWorkbook? BUT... I read on another post that there is a setting in Options/Advanced where you can manually restore the drag/drop.

Then I found another post and Jaafar answered with just the code I needed: a way to limit the Drag/Drop only on one page. The other pages are already write-protected etc. I just needed a way for the user to be able to enter data but not mess up my formulas. I'm super SUPER excited about this new functionality!

And thank YOU for your response, too!
cat