Drag and Drop mode

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
9,806
Office Version
  1. 2016
Platform
  1. Windows
Is there a VBA way to detect if the user is currently dragging and dropping a range from one location to another ? I want to be able to trap this event .
 
DragAndDrop2.xls from the latest workbook demo works perfectly now.

The only thing I noticed is that your DirectCOM.dll is 20 KB, whereas DirectCOM.dll from http://www.thecommon.net/3.html is 23 KB. Maybe you have an earlier version.


In case you are interested, here is another recent use of a similar approach for catching the user keystrokes into worksheet cells : http://www.mrexcel.com/forum/showthread.php?t=549580

I 've got other custom events in mind like detecting when a cell is being formatted ,when the user is about to perform a paste operation, when scrolling the worksheet etc...
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
DragAndDrop2.xls from the latest workbook demo works perfectly now.

The only thing I noticed is that your DirectCOM.dll is 20 KB, whereas DirectCOM.dll from Unofficial dhRichClient/vbRichClient Download Page - dhRichClient, vbRichClient, Download, DLL, ZIP, ... is 23 KB. Maybe you have an earlier version.

I know this is an old thread, but I'll give it a try.

I downloaded the updated workbook demo (DragAndDrop2.xls) and got the exact same problem John_w reported for the original workbook: an error 53 message, DirectCom file not found.

Or is one supposed to separately download DirectCom and copy it to the corresponding folder? I understood that the idea of CreateDlls and the hidden bytes sheet was to automate the creation of that file in the system32 folder.

Thanks for your help.
 
Upvote 0
DragAndDrop2.xls contains everything you need, including the bytes for the two dlls, DirectCOM.dll and DragAndDropWatcher.dll, in the hidden sheet. The code creates these files in C:\Windows\system32 if they don't exist.

I believe the code was originally written for Windows XP and it works on XP, however it doesn't work on Windows 10. The "error 53 message, DirectCom file not found" is one symptom of this. The DirectCOM.dll file is not found because it wasn't created in C:\Windows\System32, or C:\Windows\SysWOW64\ (which is where Windows automatically redirects the folder path to if the running program is a 32-bit program, i.e. 32-bit Excel; I don't know which folder is used if you're running 64-bit Excel). And the .dll is not created because of access restrictions on those folders. In any case it is not recommended to put application .dlls in the system folders.

One solution is to create the two .dlls in the workbook's folder and use ChDir to set the default folder to the workbook's folder, so that the code looks in the workbook's folder for the .dlls and the code amended in this way is posted below. However, although the code now runs without error on Windows 10, the cell drag and drop functionality has no effect.

Code:
Private Const DCOM_DLL_PATH_NAME As String _
= "C:\WINDOWS\system32\DirectCOM.dll"   'NOT USED
  
Private Const JAAFAR_DLL_PATH_NAME As String _
= "C:\WINDOWS\system32\DragAndDropWatcher.dll"  'NOT USED

'CreateObject-Replacement (FileBased)
Private Declare Function GETINSTANCE Lib "DirectCom" _
(FName As String, ClassName As String) As Object

Private Declare Function UNLOADCOMDLL Lib "DirectCom" _
(FName As String, ClassName As String) As Long

Private oDragAndDropInstance As Object
Private DLLfolder As String

'=================================================================
'Drag and Drop custom event.
'Event Procedure Must be PUBLIC !!! and located in the workbook module.
'Use the ByRef Cancel argument to prevent the drop operation.

Public Sub OnCellDrop _
(ByVal Source As Range, ByVal Target As Range, ByRef Cancel As Boolean)

    MsgBox "You are trying to drag the Range : " & Source.Address & _
    vbNewLine & " onto the Range : " & Target.Address & vbNewLine _
    & vbNewLine & "This Action is not permitted.", vbCritical
    
    Cancel = True

End Sub
'=====================================================================


Private Sub Workbook_Open()

    DLLfolder = ThisWorkbook.Path & "\"
    
    'Create the DirectCom & DragAndDropWatcher dlls.
    CreateDlls DLLfolder
    
    ChDir DLLfolder
        
    'load an instance of the 'DragAndDropWatcher.dll' Class.
    Set oDragAndDropInstance = _
    GETINSTANCE(DLLfolder & "DragAndDropWatcher.dll", "DragAndDropClass")
    'GETINSTANCE(JAAFAR_DLL_PATH_NAME, "DragAndDropClass")
    DoEvents

    If Not oDragAndDropInstance Is Nothing Then
        'start watching the Drop and Drag operations.
        Call oDragAndDropInstance.Start(ThisWorkbook)
    Else
        MsgBox "Unable to load the " & _
        "'DragAndDropWatcher' dll.", vbInformation
    End If
''
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    
    If Not oDragAndDropInstance Is Nothing Then
        oDragAndDropInstance.Finish
        Set oDragAndDropInstance = Nothing
    End If
       
    'UNLOADCOMDLL JAAFAR_DLL_PATH_NAME, "DragAndDropClass"
    UNLOADCOMDLL DLLfolder & "DragAndDropWatcher.dll", "DragAndDropClass"

End Sub



'Create the 'DragAndDropWatcher' dll and DirectCom.dll from the
'Bytes stored in the '"DllBytes" hidden worksheet.
Private Sub CreateDlls(folderPath As String)
 
    Dim Bytes() As Byte
    Dim lFileNum As Integer
    Dim aVar
    Dim x As Long
    Dim dllPath As String
    
    On Error Resume Next
    
    dllPath = folderPath & "DragAndDropWatcher.dll"
    
    'If Len(Dir(DCOM_DLL_PATH_NAME)) = 0 Then   'should be JAAFAR_DLL_PATH_NAME
    If Len(Dir(dllPath)) = 0 Then
    
       With Worksheets("DllBytes")
           aVar = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)).Value
       End With
     
       ReDim Bytes(LBound(aVar) To UBound(aVar))
       For x = LBound(aVar) To UBound(aVar)
           Bytes(x) = CByte(aVar(x, 1))
       Next
    
       lFileNum = FreeFile
       'Open JAAFAR_DLL_PATH_NAME For Binary As #lFileNum
       Open dllPath For Binary As #lFileNum
           Put #lFileNum, 1, Bytes
       Close lFileNum
    
    End If
    
    dllPath = folderPath & "\DirectCOM.dll"

    'If Len(Dir(DCOM_DLL_PATH_NAME)) = 0 Then
    If Len(Dir(dllPath)) = 0 Then
    
        Erase Bytes
        
        With Worksheets("dllBytes")
            aVar = .Range(.Cells(1, 2), .Cells(.Rows.Count, 2).End(xlUp)).Value
        End With
        
        ReDim Bytes(LBound(aVar) To UBound(aVar))
        For x = LBound(aVar) To UBound(aVar)
            Bytes(x) = CByte(aVar(x, 1))
        Next
        lFileNum = FreeFile
        'Open DCOM_DLL_PATH_NAME For Binary As #lFileNum
        Open dllPath For Binary As #lFileNum
            Put #lFileNum, 1, Bytes
        Close lFileNum
        
    End If

End Sub
 
Upvote 0
Thanks for your answer, John_w.

What I did was edit the code to generate the DLLs in the current folder and then copied both manually to c:\windows\system32 *and* c:\windows\SysWOW64, with no luck, I still got errors. My Excel is 32 bits, I don't know about my client's.

However, as you say that the drag and drop functionality is no longer working for Windows 10, I think it would be fruitless to pursue this any further. I'll have to lock elsewhere. I have, in fact, been looking with no results; care to share any ideas?

Thanks again.
 
Upvote 0
Hi jzaldivar,

Adding to John_v's kind explanation, 32bit dlls such as the ones we are dealing with here won't work with 64bit applications

Also, DirectCom is a 32bit dll so it probably won't work in your 64bit windows

My understanding is that with the advent of 64bit systems, existing 32bit dlls must be rewritten in 64bit and both dll versions pusblished with client code to choose from at run time depending on the clien'ts bit version

This is ,I am afraid, too much hassle .. I was thinking that maybe using a timer could lead us somewhere but timers will slow down the system and can make excel unstable if not handled properly

Have you tried disabling the application CellDragAndDrop feature ? This will remove the cell Fill Handle but will prevent Draging and dropping cells
 
Upvote 0
Hi, Jafaar.

Thanks for your comments.

I wouldn't want to disable drag-and-drop, I'm writing a scheduling app that will rely on a drag-and-drop interface. :-)

Right now, I implemented it keeping track of the event firing order, but I'm not completely happy with it because it's not very robust.
 
Upvote 0

Forum statistics

Threads
1,224,599
Messages
6,179,828
Members
452,946
Latest member
JoseDavid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top