Home > Software design >  How to interrupt Worksheet Deactivate?
How to interrupt Worksheet Deactivate?

Time:10-04

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
  • Related