Worksheet_Deactivate but prevent Worksheet_Activate from executing

[ExcelKnut]

Using a Worksheet_Deactivate event, the user is not allowed to leave the active sheet if comments are not entered into certain cells. When they don't enter a required comment and try to select another sheet, it runs two Worksheet_Activate events (the sheet they try to go to and when it returns) which both display message boxes. I would really like to prevent those two Worksheet_Activate events from executing.

Private Sub Worksheet_Deactivate()

Dim r As Integer ' Row where a comment is required
Dim c As String  ' Non-Production Activity where a comment is required

r = Worksheets("Non-Production").Range("CommentRequiredRow").Value
c = Worksheets("Non-Production").Range("CommentRequiredActivity").Value

If r <> 0 Then

    Worksheets("Non-Production").Activate
    Worksheets("Non-Production").Cells(r, 6).Select
    MsgBox "Please enter a comment for " & c
    Exit Sub

Else
'do nothing

End If


End Sub

 

[baitmaster]

application.enableevents = true / false as required. You will quickly need to master the use of these when working with events otherwise you are gonna get in a right mess! Trust me, been there

 

[Jerry Sullivan]

application.enableevents = true / false as required. You will quickly need to master the use of these when working with events otherwise you are gonna get in a right mess! Trust me, been there

Hi Baitmaster, Does that work for you for this scenario?

I believe that the Worksheet_Deactivate and Worksheet_Activate, events (and corresponding ThisWorkbook events) are triggered to execute prior to the first event (Worksheet_Deactivate) actually being executed. So an EnableEvents=False statement in Worksheet_Deactivate is too late to stop the the execution of Worksheet_Activate. It's still useful to disable the additional triggering of events through your code, but it won't prevent the Worksheet_Activate event that the OP is wanting to avoid.'

One workaround would be to use a Worksheet_Deactivate procedure similar to the one in the OP, and set a global variable flag to True in the if the user has not met the comment entry requirements. Any Worksheet_Activate events in your workbook could test that this flag is False before executing code (like your MsgBox statements) that is not desired if the user is being sent back to the comment entry sheet.

 

[baitmaster]

ouch, hadn't thought of that!

Yeah, might need to extend the principles and come up with a custom "proceed" flag and use accordingly. I said you can quickly get in a mess when working with events and I think this proves it sooner than I expected...