Stopping drag and drop in Excel via VBA

The ability to drag and drop in Excel is great when you know what you are doing. The downside is that it is also easy for inexperienced users to affect the structure of your spreadsheet by using drag and drop techniques.

Drag and drop means using the arrow cursor or the fill handle to change your spreadsheet – see image below.

Fill Handle and Arrow Cursor

Inexperienced users may inadvertently drag things around a sheet by using the wrong mouse cursor for the wrong thing.

If you are comfortable with VBA, you can use it to turn off the drag and drop functionality for certain files.

The command to turn off drag and drop is

Application.CellDragAndDrop = False

The command to turn it back on again is

Application.CellDragAndDrop = True

Always remember to turn it back on after you have turned it off.

This affects all the open Excel files, so I would only use it sparingly and only on those files where you need to stop users accidentally moving cells around.

The Excel Options setting to turn the setting off and on is shown below.

Excel Options - drag and drop

Click here to see how you can run a Macro off a Quick Access Toolbar icon.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.