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
'''
CodePudding user response:
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.