Warning with option to cancel before worksheet_deactivate

spacebouncer

Board Regular
Joined
Feb 7, 2014
Messages
109
Hi. I want to wipe a load of content when the worksheet is deactivated. However, I'd like to warn the user first. I'm happy with the MsgBox function, and using return values, but how to I apply this to cancel a worksheet_deactivate? Thanks
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
What do you want to happen if the user wants to leave the sheet, but doesn't want its contents cleared?
 
Upvote 0
Hi Joe.

Ideally, when the user tries to activate another worksheet(or deactivate current), I'd like them to be warned that this might cause the content of the current sheet to be lost, and to give them the option to cancel. The contents of the sheet are created from data elsewhere, so is disposable. I just want to give the warning and option to stay in the current sheet in case the user makes any changes of their own to the content. Thanks
 
Upvote 0
Hi Joe.

Ideally, when the user tries to activate another worksheet(or deactivate current), I'd like them to be warned that this might cause the content of the current sheet to be lost, and to give them the option to cancel. The contents of the sheet are created from data elsewhere, so is disposable. I just want to give the warning and option to stay in the current sheet in case the user makes any changes of their own to the content. Thanks
The deactivate event doesn't include a cancel option. Here's an alternative you can consider. In this approach, the user is immediately returned to the original sheet if he/she does not want content cleared. Of course, this means they can't leave the sheet. If they accept leaving then the deactivate event code calls the macro that clears content. Insert the macro call where the comment in red appears.
Rich (BB code):
Private Sub Worksheet_Deactivate()
Dim ans As Integer
ans = MsgBox("Leaving this sheet will cause its content to be removed - are you sure you want to leave?", vbCritical + vbYesNo, "ALERT!")
If ans = vbNo Then
    Me.Select
Else
'    Call code to delete contents
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top