Error 1004 when cancelling File dialog box in Excel VBA

Posted on 2014-07-17
Last Modified: 2014-07-23
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?
Question by:fjkilken
    LVL 9

    Accepted Solution

    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))            
                MsgBox "Dialog Cancelled"
            End If
       End With
       If Err.Number = 1004 Then
            MsgBox "Cancelled write protect screen"
        End If
    End Sub

    Open in new window


    Author Closing Comment

    Worked fine - thanks!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    This article describes how to add a user-defined command button to the Windows 7 Explorer toolbar.  In the previous article (, we saw how to put the Delete button back there where it belongs.  "Delete" is …
    Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
    This video discusses moving either the default database or any database to a new volume.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now