Worksheet_Deactivate but prevent Worksheet_Activate from executing

ExcelKnut

Board Regular
Joined
Apr 18, 2013
Messages
140
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.

Code:
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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
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

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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.
 
Last edited:

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,028
Members
414,039
Latest member
southike

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
Top