• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2005
  • Last Modified:

Error 1004 when cancelling File dialog box in Excel VBA

Hi
I have some VBA code that opens a file.
The file itself is write-protected, so before it opens, a dialog box appears asking the user to either:
Enter the password and click OK button
Open as Read-Only
Cancel button (to cancel opening the file)

My problem is when the Cancel button is clicked, the VBA thows an error 1004 (error 1004 method open of object workbooks failed)
How can I handle / catch the case where a user clicks the Cancel button?
Thanks
Fergal
0
fjkilken
Asked:
fjkilken
1 Solution
 
dustockCommented:
This worked for me.  If you don't want the MsgBox you could always 'Exit Sub' or anything else you want.

Private Sub ChooseFile()
   Set fd = Application.FileDialog(msoFileDialogFilePicker)
   With fd
        If .Show = True Then
            On Error GoTo ErrHandler:
            Workbooks.Open (fd.SelectedItems(1))            
        Else
            MsgBox "Dialog Cancelled"
        End If
   End With
   
ErrHandler:
   If Err.Number = 1004 Then
        MsgBox "Cancelled write protect screen"
    End If
End Sub

Open in new window

0
 
fjkilkenAuthor Commented:
Worked fine - thanks!
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now