Close another workbook error with code before closing

user_17584830 0
Viewed 59

I have a code in my "current" workbook that close "other" workbook. However that "other" workbook has a code that when you close it, a code will be executed.

What I want is that when I run the code from my "current" workbook, it will bypass or ignore the code of another workbook.

Current Workbook Code:

NewName = "v10.0_" & Left(CurFile, Len(CurFile) - 5)
pseudo = "'"
MacroName = "Workbook_BeforeClose"
    
Call SaveAs(wbk_r, NewName, FolderPath) 'Save the file to destination folder
    
Application.Run pseudo & NewName & "'!" & MacroName & ".xlsm", Cancel

Other Workbook Code: '''

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If MsgBox("All data input will be permanently saved in the file database! Are you sure for your update?", vbYesNo) = vbNo Then
        Exit Sub
    Else
        Call SaveChangesA
        ThisWorkbook.Saved = True
    End If
End Sub

'''

vba excel
1 Answers

Use Application.EnableEvents = False to turn off events. So the event Workbook_BeforeClose will not run if you close the workbook.

Make sure you turn it on afterwards (or in case of an error) Application.EnableEvents = True otherwise events will stay turned off in your entire Excel.

To give an Example for the error handling

Option Explicit

Public Sub Example()
 
    ' your inital code before closing the workbook goes here …

    
    Application.EnableEvents = False
    On Error Goto ERR_EVENTS
    
    ' close your workbook here (nothing else)

    On Error Goto 0 ' re-enable error reporting for the rest of your code!
    Application.EnableEvents = True


    ' your other code goes here …

    Exit Sub  ' run the following only in case of an error
ERR_EVENTS:

    ' Enable events!
    Application.EnableEvents = True
    
    Err.Raise Err.Number  ' raise an error in case there was one (otherwise you will never know that something went wrong).
End Sub

For more see VBA Error Handling – A Complete Guide.

user_32196130
Related