Yes, you can identify Drag and Drop Event!

[tysk]

I've seen a lot of people asking for this, so this is not a question but just information for you to use if you need. 'Drag and drop' is an excellent way to ruin cell reference links, so I think this info might be welcome.

This uses the fact that if you do not have a 'Application.EnableEvents = False' in your worksheet code, the change event fires twice and you can identify that. At first, I used a public counter to know if it was the first or second one, but then I found this way that is more elegant, I think.
I describe a rather clean code here, I leave out a lot of error trapping etc:
---

1. In a module you define:
Public SelectCl As String

2. In your worksheet code you use this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If TypeName(Selection) = "Range" Then
Let SelectCl = Selection.Address
End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> SelectCl Then
MsgBox "## Drag and Drop identified!"
Else:
MsgBox "## NotDragDrop OR first time D&D loop"
End If
End Sub



---
You CANNOT use it together with Application.EnableEvents = False
It will not identify a drag from another workbook
It also reacts to the format painter event

Can you find any problems etc. with this, I am happy to know from you. Together with workarounds

Good luck,
Tyrone Skogström

 

[Jaafar Tribak]

I guess this is a good candidate for using the windows API to achieve similar functionality

 

[tysk]

Yes, I find if the users aren't within your company etc. it feel's more safe to keep the whole application within excel's functions, and you do not risk to fire any antivirus programs etc.

 

[Jaafar Tribak]

Hi Tyrone Skogström,

Please take a look at this thread and see if it works for you :
http://www.mrexcel.com/forum/excel-questions/842953-drag-drop-pseudo-event.html

 

[traveler4]

@tysk's code works well for me.

If, during your drag-and-drop actions, Excel bugs you by saying

this warning can be switched off in Excel's options:

Credits go to ExcelHowto. Their other solution

Application.DisplayAlerts = False

did not work for me.

 

[ADay365]

Found this really useful thanks.
I also needed this to undo the last action, but due to the enabling events it was slightly more tricky.
A nice solution that worked for me was to call the application.undo part of it

Updated method here

1. In a module you define and add sub:

Public SelectCl As String

Sub UndoLastAction()
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End Sub

2. In your worksheet code you use this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If TypeName(Selection) = "Range" Then
Let SelectCl = Selection.Address
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Protect UserInterfaceOnly:=True
If Target.Address <> SelectCl Then
Call UndoLastAction
MsgBox "## Drag and Drop identified! - Please do not move cells as this will cause #REF errors"
Else
End If
End Sub