Prevent "File format is not valid message" after dropping file on userform-CodePudding

I have created Drag-n-Drop form for Excel in order to capture link to file location using treeview control (code bellow). It works as intended, however problem that after I make form ShowModal = False (because user might want to move Excel window in order to reach file-to-be-dragged) after it runs it's routine, error message pops-up informing that "File format is not valid" (screen below) or notification that file might be corrupt or unsafe (second screen below).

To my understanding that happens because Excel considers file to be dropped on the sheet and tries to open it (it will be most likely .pdf file).

Is there a way to prevent that other than making form Modal? To my understanding to achieve that somehow error message should be prevented or Excel should not try to open file at all and by doing that avoid message altogether (best case).

Code for Drag-n-Drop functionality:

Private Sub TreeView1_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)
'for capturing draged file path
'VBA does not have normal native functionality to do that so it is solved by using treeview widget unconventionaly

Dim LinkToPass As String

LinkToPass = Data.Files(1)

MsgBox "Thank you! Link captured.", vbInformation, "Link captured"

'Pass information to another form, where user enters all other data required
If formLoaded("NewEntry_agreement") Then 
    NewEntry_agreement.LinkToFile.Caption = LinkToPass
End If

CloseBtt_Click 'just call close button Sub with Unload Me inside

End Sub

EDIT: Additional info and screenshot about alternative message. Also made goal more clear - either prevent message or prevent Excel from trying to open the file and by doing that prevent error message.

CodePudding user response：

Click the form to toggle modal/modeless

' Adapted from Stephen Bullen's ModelessForm.xls 1998 example
Private Declare PtrSafe Function EnableWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal fEnable As Long) As Long

' click the form to toggle modal/modeless

Private Sub UserForm_Click()
Static lMode As Long

    lMode = IIf(lMode = 0, 1, 0)
    EnableWindow Application.hwnd, lMode
    Me.Caption = IIf(lMode, "Modeless", "Modal")

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
' ensure the app window is reset (maybe trap the state and reset if/as necessary)

    EnableWindow Application.hwnd, 1

End Sub

CodePudding user response：

I understand what you describe but I don't think there's a simple solution. Easiest would be if this works -

Call DragAcceptFiles(FindWindow("ThunderDFrame", Me.Caption), 0)

.. but unfortunately it doesn't. Neither does does attempting to disable the xlApp.Hwnd the same way from accepting dropped files. Maybe need to disable one of the other bunch of windows(?), I've only tried the ones I mentioned.

Briefly two different approaches you might look into -

Add a button for the user to toggle modeless / modal before enabling drag files operation. I don't have the code to hand, but it's definitely possible albeit not supported.
Instead of the treeview's OLE-DD setup a callback with CallWindowProc to trap the WM_DROPFILES message, get the files with DragQueryFile, and prevent Excel from receiving the message with DragFinish. You'd need a window and this could be the form's first and only direct Child window. Better though to add a window'd control such as a Frame (though it doesn't directly expose its 'hwnd' so a fair bit of API work to get it). Plenty of general examples out there and I've had this approach working - but unfortunately there are several catches and I don't have anything reliable enough I'd want to post!

This won't be the answer you're looking for but it might be the best you're going to get! Though I'd be pleased to be wrong:)