VBA - IF Run Time Error 1004 then show Userform + exit

[Alphacsulb]

When the file name doesn't exist then I get a run time error 1004. Which I know means that the file doesn't exist. However, I want to show/create a userform that tells other people using this worksheet the reason why it didn't work so that they can correct it themselves instead of asking me every time.

This is the code that looks for the file name in I2, in the folder Reports

ChDir "M:\Reports" 'Where to start looking in
    Workbooks.Open Filename:="M:\Reports\" & ActiveSheet.Range("I2") ' Opens the Billing file, the file name is in I2

What I would like to do is add the following:
IF the file does not exist (creates the run time error 1004) then
ReasonForError.Show
Exit Sub

Would this go in front or before my current piece of code?

 

[Mike Blackman]

Hi,

Maybe something like this:

    On Error GoTo Finish
    
    ChDir "M:\Reports" 'Where to start looking in
        Workbooks.Open Filename:="M:\Reports\" & ActiveSheet.Range("I2") ' Opens the Billing file, the file name is in I2
    
Finish:
    
    If Error.Number <> 0 Then
        ReasonForError.Show
        Exit Sub
    End If

 

[Mike Blackman]

Or If your handling other errors and you only want this to show for error 1004 then maybe this:

    On Error GoTo Finish
    
    ChDir "M:\Reports" 'Where to start looking in
        Workbooks.Open Filename:="M:\Reports\" & ActiveSheet.Range("I2") ' Opens the Billing file, the file name is in I2
    
Finish:
    
    If Error.Number = 1004 Then
        ReasonForError.Show
        Exit Sub
    End If

 

[VoG]

Try like this. You doin't need ChDir

If Dir("M:\Reports\" & ActiveSheet.Range("I2")) = "" Then
    MsgBox "File doesn't exist", vbexclamation
    Exit Sub
Else
    Workbooks.Open Filename:="M:\Reports\" & ActiveSheet.Range("I2") ' Opens the Billing file, the file name is in I2
End If

 

[Alphacsulb]

Both versions worked. I ended up using MsgBox, as it saved the step of creating the UserForm.