Search code examples
excelvbauserform

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

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).

enter image description here

My guess that error depends on the file format

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.

Solution

  • 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