VBA Excel I run a group of macros and need to have an error handler for one of the macros

[cizzett]

So I run a group of called macros and I have an error handler in the "update SOS" macro, what I want to figure out is how to do an error handler which will terminate the whole macro bundle if "No" is selected. I currently have a message box to inform the user the SOS is not being updated but not sure how to set up the error handler to allow them to terminate the entire macro bundle instead of just the one.

If I put the error handler on the main bundle if will do the same message if any macro fails wont it? I just want it to give the option if the "Update SOS" macro errors.

Hope this makes sense

Sub ImportBndle()

Dim Msg As String, Ans As Variant


    Msg = "Please paste yesterdays data before continuing. If you have already completed this press OK to conimue or press Cancel to go back and complete."


    Ans = MsgBox(Msg, vbYesNo)


    Select Case Ans


        Case vbYes
        
        WaitingMsg.Show
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
 '-----------------------------------------------------------------------------
    Call UpdateSOS
    Call SortDFColumns
    Call ImportAllData
    Call ReadBatch
        ActiveWorkbook.RefreshAll
    Call MarkNew
    Call SOSSort
    Call StatusSort
        
       Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    Unload WaitingMsg
            
        Case vbNo
        GoTo Quit:
    End Select


Quit:
         
Call Hidesome


End Sub

Here is the "Update SOS" Macro that is being called in the macro bundle as it is currently:

Sub UpdateSOS()

' ImportData Macro
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Sheets("SOS")


        On Error GoTo ErrHandler
    
    Dim df As Workbook: Set df = Workbooks.Open(ws.Range("M1").Value)
    Dim ds As Worksheet: Set ds = df.Sheets("SOS-M")
    
    Application.DisplayAlerts = False


  ' This section copies the data and pastes into the Workbook


    ds.Range("A:G").Copy
    ws.Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    df.Close SaveChanges:=False
    Exit Sub
ErrHandler:
    MsgBox ("SOS Failed to Update, Check SOS FIle Address and File Name on hidden SOS Sheet")
End Sub

 

[Yongle]

Stop the active procedure only with

Exit Sub

Stop everything with

End

 

[cizzett]

Good Call,

I changed my "Update SOS" macro to encompass a Yes/No answer when errored including End for the No ans.

Sub UpdateSOS()

' ImportData Macro
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Sheets("SOS")
Dim Msg As String, Ans As Variant
        On Error GoTo ErrHandler
    
    Dim df As Workbook: Set df = Workbooks.Open(ws.Range("M1").Value)
    Dim ds As Worksheet: Set ds = df.Sheets("SOS-M")
    
    Application.DisplayAlerts = False


  ' This section copies the data and pastes into the Workbook


    ds.Range("A:G").Copy
    ws.Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    df.Close SaveChanges:=False
    Exit Sub


ErrHandler:
    Msg = "SOS Failed to Update, Check SOS FIle Address and File Name on hidden SOS Sheet. Select YES to continue anyway or NO to exit"
Ans = MsgBox(Msg, vbYesNo)


    Select Case Ans
        Case vbYes
        GoTo cnt:
        Case vbNo: End
        End Select
cnt:
End Sub

 

[Fluff]

One word of warning about using End.
If you have any variables declared outside the code (ie global, public or private variables)these will be lost when using End

 

[Yongle]

You could simplify the last few lines

replace

    Ans = MsgBox(Msg, vbYesNo)

    Select Case Ans
        Case vbYes
        GoTo cnt:
        Case vbNo: End
        End Select

with

    If MsgBox(Msg, vbYesNo) = vbNo Then End