Apply 'On error GoTo' only for specific range

Question

I would like to apply "On Error GoTo err1" only for the next few lines in the code:

On Error GoTo err1
ActiveWorkbook.SaveAs Filename:="C:\project\" & Year(Date) & "\" & _
                   MonthName(Month(Date)) & "\" & MyFileName & ".xls"
ActiveWorkbook.Close SaveChanges:=False

That's it, after that I want to disable error handling. I tried to add "On Error resume" but no results whatsoever. err1 handling looks like this:

    err1:
    MsgBox ("Project will not overwrite the file"), vbCritical
    ActiveWorkbook.Close SaveChanges:=False

This sub saves one sheet of the file into new .xls file, it is automatically saved by date. In case there is already a file generated, and a person which will run the macro clicks on "No" when asked to overwrite the file, this err1 will pop out the message and will prevent user from getting debug message. However, this error handling seems to be going through all the code which is not good - after saving the file sub is generating an auto email, and if the person wants to go back to the project before clicking the "send" button it goes to the same err1 and closes the original workbook (not separate sheet that happens the first time) and leaves the user speechless. I want to avoid such situations as users with bad Excel skills will make these reports.

Any suggestions?

If you're done with an On Error Goto xxx handler you can turn it off with On Error Goto 0 Better yet, use Dir() to check for the existence of the file before calling the SaveAs operation. — Tim Williams
Hello Tim, interesting, but on error goto 0 would not disable the handler. I can check for dir, but I would like to give also some space for the user to "rerun" in case he did something wrong. Cant understand why onerror handler keeps enabled through all the rest of the macro. — user3812753
@user3812753 I kinda disagree. When you call On Error Goto 0, it will not re-call the handler. Did you place or error handlers correctly? See my post. — L42
@user3812753 - you are mistaken that On Error Goto 0 would not cancel your current error handler, as @L42 shows in the answer below. I'm not clear what you're really asking, so it would help to include more relevant code if L42 hasn't already cleared up all of your concerns. — Tim Williams
Hello Tim and L42, seems like L42 has cleared all of the concerns. It is very strange that I was not able to turn off the error handler until I have restarted my computer... — user3812753

L42 L42 · Accepted Answer · 2015-04-23T01:29:10

This is actually not an answer but I cannot fit it to comment.

I assume you did something like below. So you kinda sandwich your suspected line producing error with OEG and OEG0 like below:

Sub test()

    On Error GoTo err1
    'Access non existing named range
    Range("ProduceError").Select
    'Above will produce error and your handler kicks in
    On Error GoTo 0


    MsgBox "First error handled successfully"

    'Access invalid range address
    Range("B0").Select
    'Above will error out normally and err1 not called
    'OEG<Label> only called once and not in the entire macro

    Exit Sub
err1:
    MsgBox Err.Description
    Resume Next

End Sub

Now you see, your error handler is not called again after OEG0 line.
Normal error will be raised after a second error producing line is encountered.
Now, you might want to check this out to review error handling.

Apply 'On error GoTo' only for specific range

1 Answers