All geek questions in one place

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

Is there a way to recognize a drag event on an MS Excel worksheet? What I'm looking for is the ability to listen to an event when dragging a file (say, from the desktop) onto a cell in an MS Excel worksheet (and have the file name inserted into the cell).

Can this be achieved at all with Excel macros?

+4
source share
1 answer

I myself was not sure how to complete the task, however, it seems that someone has already tried to solve this problem. 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 set the file name if it is placed in a text field. You can use a method, function, or even a separate routine to use the text that was placed in the text box.

For example, by reviewing the article fooobar.com/questions/1411587 / ... about copying text from a text field to a cell, you can use this code to enter text into a range on your excel sheet

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

From there, the question of linking routines to another macro for one form of automation or another, drag and drop as you see fit, or something for you.

Let me know if this helps,

~ Jol

+1
source

Source: https://habr.com/ru/post/1411585/

More articles:


All Articles