Excel Drag/Drop to Get Filename and Path-CodePudding

I have a user form "UserForm1" and am using the following code to obtain the filename and path of a file that the user has dragged and dropped into the TreeView located on the userform.

 Public Sub TreeView1_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)
    StrPath = Data.Files(1)
    Debug.Print StrPath
    Call PrintPath
End Sub

Then in the UserForm_Initialize I have

TreeView1.OLEDropMode = ccOLEDropManual

I know this code is getting the path and name because I'm able to debug.print it. However, my issue is I can't get this filename and path to be utilized in a module. For instance I have tried to do the following for the simplest of uses (to print the filename and path to cell A1):

    Public Sub PrintPath()
UserForm1.TreeView1.StrPath = Range("A1").Value

'StrPath.Value = Range("A1").Value

'UserForm1.StrPath.Value = Range("A1").Value

'Range("A1").Value = UserForm1.Data.Files(1)

End Sub

All of the commented lines are other versions I have attempted with no avail. I typically get Object does not exist. Sometimes 424 errors. Can anyone point me in the right direction?

Please and thank you!

Chris

CodePudding user response：

The typical way to do this woould be to pass the path as an argument to PrintPath

Public Sub TreeView1_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, _
                     Button As Integer, Shift As Integer, x As Single, y As Single)
    Dim strPath
    strPath = Data.Files(1)
    Debug.Print strPath
    PrintPath strPath '<< pass in the path
End Sub

Public Sub PrintPath(sPath)
    ActiveSheet.Range("A1").Value = sPath
End Sub

CodePudding user response：

I have just now figured out the answer. In the UserForm1 Code I did

Option Explicit
Public StrPath As Variant

Then in the module I was able to use it with

UserForm1.StrPath

i.e.

Range("A1").Value = UserForm1.StrPath