CODEWITHSUNDEEP
eventsvbaexceldrag-and-drop
freedayum
freedayum

Reputation: 311

Listening for mouse (drag-and-drop) events in MS Excel

Is there a way to recognize a drag and drop event on a MS Excel sheet? What I am looking for is to be able to listen to the event when you drag and drop a file (say from desktop) onto a cell in a MS Excel sheet (and have the file's name inserted into the cell).

Can this be achieved at all with Excel Macros?.

Upvotes: 4

Views: 11197

Answers (1)

Scott Conover
Scott Conover

Reputation: 1421

I myself was unsure how to perform the task - however, it appears someone has already attempted to tackle the issue. I pulled this code from vbadud.blogspot:

' Place file on textbox to display filename.
Private Sub TextBox1_OLEDragDrop(Data As DataObject, Effect As Long, Button As Integer, Shift As Integer, X As Single, Y As Single)

' Declare variable(s).
Dim eventvar1 As Integer '* Files Counter

' If an error occurs, go to the Error Handler.
On Error GoTo Error_Handle1
'Drag N' Drop Event Handler
If Data.GetFormat(vbCFFiles) = True Then
eventvar1 = Data.Files.Count
    If eventvar1 = 1 Then
        If InStr(1, LCase$(Data.Files(eventvar1)), ".xls") Then
            txtExcel.Text = Data.Files(eventvar1)
        End If
    End If
End If

   ' Error Handler
    Error_Handle1:
        If Err <> 0 Then
            Debug.Assert Err = 0
            Err.Clear
        End If
    End Sub

The code will post the name of the file if it is put into the textbox. You can use a method, function or even a separate subroutine to make use of the text which has been placed into the textbox.

For instance, examining an SO article on copying text from a textbox to a cell, you can use this code to input the text into a range on your excel sheet:

Range("A2").End(xlDown).Offset(1, 0).Value = TextBox1.Text

From there, its a matter of either tying the subroutines to another macro for one form of automation or another, dragging and dropping as you see fit, or whatever makes sense for you.

Let me know if that helps,

~JOL

Upvotes: 1

Related Questions