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

Alphacsulb

Alphacsulb

Active Member
Joined
Mar 20, 2008
Messages
414
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

Code: 
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?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Hi,

Maybe something like this:

Code: 
    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
 
Upvote 0
Or If your handling other errors and you only want this to show for error 1004 then maybe this:

Code: 
    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
 
Upvote 0
Try like this. You doin't need ChDir

Code: 
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
 
Upvote 0
Both versions worked. I ended up using MsgBox, as it saved the step of creating the UserForm.
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
Runtime Error
Replies
2
Views
130
Holley
H
J
  • Question
Run-time error '1004': The extract range has a missing or invalid field name.
2
Replies
15
Views
692
josros60
J
C
  • Question
Script crashes - sometimes
Replies
7
Views
205
cowboysnut
C
B
  • Solved
Method 'SaveAs of Object'_Workbook' failed error handling routine help needed.
Replies
3
Views
532
rlv01
R
D
  • Solved
Run-time error '1004': Application-defined or object-defined error
Replies
4
Views
460
rollis13
rollis13

Forum statistics

Threads
1,214,585
Messages
6,120,399
Members
448,958
Latest member
Hat4Life

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top