Warning with option to cancel before worksheet_deactivate

[spacebouncer]

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

 

[JoeMo]

What do you want to happen if the user wants to leave the sheet, but doesn't want its contents cleared?

 

[spacebouncer]

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

 

[JoeMo]

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.

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

 

[spacebouncer]

Hi Joe. Thats a much better way to do it! Much appreciated.

 

[JoeMo]

Hi Joe. Thats a much better way to do it! Much appreciated.

You are welcome - thanks for the reply.