how to stop excel from running through all the errors

Question

I have the following code to handle my errors:

Sub some_sub()

    On Error GoTo error1
    Some code here

    On Error GoTo error2
    Some more code here

    On Error GoTo error3
    final piece of code here



Exit Sub

error1
    MsgBox "Claims followup.xlsm is not open." & Chr(10) & Chr(10) & "Open the file in read/write"

error2
    MsgBox "Please make sure that the Claims followup file is open." & Chr(10) & Chr(10) & "If the file is open make sure that you the Solicitation Number is written correctly."

error3
    MsgBox "Your Claim followup file is in -read only- mode. Your changes may not be saved"

End Sub

Sub some_sub()

    On Error GoTo error1
    Some code here

    On Error GoTo error2
    Some more code here

    On Error GoTo error3
    final piece of code here



Exit Sub

error1
    MsgBox "Claims followup.xlsm is not open." & Chr(10) & Chr(10) & "Open the file in read/write"

error2
    MsgBox "Please make sure that the Claims followup file is open." & Chr(10) & Chr(10) & "If the file is open make sure that you the Solicitation Number is written correctly."

error3
    MsgBox "Your Claim followup file is in -read only- mode. Your changes may not be saved"

End Sub

However, when the "error1" is detected it fires "error2" and "error3" as well, but I only want it to fire "error1". If "error2" fires "error3" fires as well. And when error3 is fired it is the only one, so it is running from top to bottom from the error it found.

My question is: How can I change this code so it only fires the error one of the errors?

Thanks in advance for any help.

Answer 1

Try to avoid the error in the first place. For unexpected errors use a Select...Case in your error handler and have the code exit the procedure in the same place so all the required tidying up is performed.

I've added a couple of function that check files are open or exist.

Sub some_sub()

    Dim x As Long
    Dim y As Long
    Dim wrkBk As Workbook
    Dim wrkSht As Workbook
    Dim sPath As String, sFile As String

    On Error GoTo ErrorHandler

        sPath = "C:\"
        sFile = "Claims followup.xlsm"

        'Avoid error by checking file exists and is open.
        If FileExists(sPath & sFile) Then

            'Avoided Error1 & 2 completely by checking if the file is open first.
            If WorkBookIsOpen(sPath & sFile) Then
                Set wrkBk = Workbooks(sFile)
            Else
                Set wrkBk = Workbooks.Open(sPath & sFile)
            End If

            'Avoid error 3 by checking if it's read only first.
            If Not wrkBk.ReadOnly Then

                'These all throw errors.
                x = "A"
                y = 0
                Debug.Print 20 / y

                Set wrkSht = ThisWorkbook.Worksheets("Non Existent Worksheet")

            End If

        End If

            On Error GoTo 0

SingleExitPoint:
    'Tidy up, close connections, etc....

Exit Sub

ErrorHandler:
    Select Case Err.Number
        Case 13 'Type Mismatch
            x = 0
            Resume Next 'Continue on the line following the error.
        Case 11 'Division by zero
            y = 2
            Resume 'Continue on the line that caused the error.
        Case 9 'Subscript out of range
            Resume SingleExitPoint

    End Select

End Sub

Public Function WorkBookIsOpen(FullFilePath As String) As Boolean

    Dim ff As Long

    On Error Resume Next

    ff = FreeFile()
    Open FullFilePath For Input Lock Read As #ff
    Close ff
    WorkBookIsOpen = (Err.Number <> 0)

    On Error GoTo 0

End Function

Public Function FileExists(ByVal FileName As String) As Boolean
    Dim oFSO As Object
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    FileExists = oFSO.FileExists(FileName)
End Function

Answer 2

If you are trying to differentiate between error you can do so using Err.Number and catch the specific error and show message accordingly so in you case it should be:

    `Sub some_sub()

        On Error GoTo errorHandler
        Some code here   


    Exit Sub
    errorHandler:
if Err.Number= Err no corresponds to Claims followup.xlsm is not open
    MsgBox "Claims followup.xlsm is not open." & Chr(10) & Chr(10) & "Open the file in read/write"
elseif Err.Number = Err no corresponds to Solicitation Number is written correctly
        MsgBox "Please make sure that the Claims followup file is open." & Chr(10) & Chr(10) & "If the file is open make sure that you the Solicitation Number is written correctly."    
elseif Err.Number = Err no corresponds to Claim followup file is in -read only- mode
        MsgBox "Your Claim followup file is in -read only- mode. Your changes may not be saved"

    End Sub

Hope this helps!

Answer 3

Try adding a line Exit Sub after the message box. Execution continues after the messagebox for error1.

So it should look like this:

Exit Sub

error1
    MsgBox "Claims followup.xlsm is not open." & Chr(10) & Chr(10) & "Open the file in read/write"
    Exit Sub
error2
    MsgBox "Please make sure that the Claims followup file is open." & Chr(10) & Chr(10) & "If the file is open make sure that you the Solicitation Number is written correctly."
    Exit Sub
error3
    MsgBox "Your Claim followup file is in -read only- mode. Your changes may not be saved"
    Exit Sub
End Sub