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