Forum Discussion
Unintended effect VBA code for drag and drop
Hi,
I am working on making an excel spreadsheets for my colleagues to fill in, not that they are not Excel savvy. The issue with my spreadsheet is that:
- 1. it has many lines with formulas/equations, and if the user would drag and drop any of the cells they are allowed to edit, they will brick the references of the formulas.
- 2. also, the cells they are allowed to edit are "data validation" cells, meaning that the data validation will be removed if they drag and drop the cell.
So, I thought it would be best to block the drag and drop function. I am doing it with the following VBA codes :
------------------------------------
Private Sub Workbook_Activate()
Application.CellDragAndDrop = False
End Sub
-----------------------------------
Private Sub Workbook_close()
Application.CellDragAndDrop = True
End Sub
-----------------------------------
The issue here is that. I can only copy data from my spreadsheet to other spreadsheets, and not the other way around. I have asked several people (and Chatgpt, of course). But there seems to be no easy fix for this issue. It is crucial for the people filling in this spreadsheet to copy data from other spreadsheets to the one that I am sending them.
So, any help regarding this issue would be super appreciated!
Best regards,
Aram
3 Replies
"If the user would drag and drop any of the cells they are allowed to edit, they will brick the references of the formulas"
That is a 'feature' inherent in the way in which spreadsheets are conceived and built (or, at least, an unintentional consequence)! With traditionally-built spreadsheets, developers frequently have cause to shake their heads over the manner in which 'stupid' users butcher the spreadsheet by failing to appreciate that 'drag and drop' is a capital offence!
I prefer to accept that drag and drop can be a very efficient way of correcting data input errors, and try to write formulas to accept such steps. I approach this by writing Excel 365 array formulas (and using structured references) for all data processing steps. These are far more robust than the traditional single-cell relative referencing when it comes to moving the furniture about.
Doesn't help with the data validation or conditional formatting, though. Those are ancient pieces of code that are no longer fit for purpose, but it will take a massive effort to bring them into the 21st century.
- Thanks for the advice Peter, I will keep this in mind when making new spreadsheets!
Best regards,
Aram
Setting CellDragAndDrop to False clears the clipboard, unfortunately. So you'll have to choose: either allow drag-and-drop AND copy/paste from outside, or disable both...
