ListView in Excel doesn't act like expected when using drag&drop

user_5126877 0
Viewed 248

I want to write down the path of the file I am dragging into Cell C6.

When I use a ListView inside a form, it works well with this code:

Private Sub ListView1_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)
                   
    Sheets("Sheet1").[C6].Value = Data.Files(1)
   
    End Sub

When I try the same inside excel (without a form) it doesn't recognize the drag and drop command on the ListView, instead Excel tries to open the file I just dragged and dropped, no matter where I drop it. OLEDragMode and OLEDropMode are set to manual.

What I want is that the path of my file is written into cell C6 without using forms. Any ideas why that doesn't work?

vba excel
2 Answers

Sounds like you need to work with the Path property of Workbook objects. This should do what you want:

Private Function getPath(wb As String)
    Dim wkb As Workbook
    Set wkb = Workbooks(wb)
    getPath = wkb.Path
End Function

Sub test()
    Debug.Print (getPath(ThisWorkbook.Name))
End Sub
'Return: C:\Users\FR\Desktop
user_82561780

You need to turn off Design Mode.

You can quickly tell that Design Mode is on if you right-click the ListView and it gets selected.

Before turning Design Mode off make sure you add the code you wrote in the question but this time it needs to sit within the corresponding worksheet object. Simply double-click the ListView to get there. I'm sure you already know this. Also, make sure you change the OLEDropMode to manual by clicking Properties in the Developer ribbon tab (the button is right next to the Design Mode button).

Finally, simply go to the Developer tab and toggle off Design Mode.
enter image description here

The desired action will now work.

If you don't have the Developer tab on, then just right-click on the Ribbon and then on Customize the Ribbon to turn it on:
enter image description here

user_84889130
Related