How to interrupt Worksheet Deactivate?-CodePudding

Using Excel 365 on Windows 10. Edited for clarity.

My workbook has several "Payday" sheets, and a sub called SavePayday which copies values from that sheet to another.

I want to trigger SavePayday when the user goes from a Payday sheet to another tab. I can do that by calling it in the Workbook_SheetDeactivate area.

But I want to give the user the option of cancelling the move: by not running SavePayday and staying on Payday. A simple "Are you sure you want to save this? Yes/No" message box.

Here on SO, someone mentioned that we could "interrupt" Workbook_SheetDeactivate before the user actually leaves the sheet. It was mentioned briefly in a comment, without fully being explained.

How can I trigger that message box so that the user can still see the Payday sheet, and if the answer is no, stay on the sheet instead of switching?

CodePudding user response：

Private Sub Worksheet_Deactivate(ByVal Sh As Object)
    Dim ws: Set ws = ActiveSheet
    Application.EnableEvents = False
    sh.Activate
    Application.EnableEvents = True
    If MsgBox("Are you sure ?", vbYesNo) = vbYes Then
        Application.EnableEvents = False
        ws.Activate
        Application.EnableEvents = True
    End If
End Sub