Worksheet Activate and Deactivate Events

Following is a very simple example that is executed whenever a particular worksheet is activated. This code simply pops up a message box that displays Welcome Back message:

Private Sub Worksheet_Activate()
 MsgBox "Welcome Back!"
End Sub

The second example demonstrates how to automatically refresh all pivot tables whenever the particular worksheet is activated:

Private Sub Worksheet_Activate()
 Dim i As Integer
 For i = 1 To ActiveSheet.PivotTables.count
  ActiveSheet.PivotTables(i).PivotCache.Refresh
 Next i
End Sub

Hide or disable shortcut menu item

Excel shows you a shortcut menu when you right-click. This menu varies at different Excel parts. For example, when you right-click on a ribbon, a different menu appears, but when you right-click on a cell, a completely different menu appears. In the following example, we’ll disable a shortcut menu item “Cut” (which was added to the Cells menu) when the Sheet1 is activated:

Private Sub Worksheet_Activate()
 CommandBars("Cell").Controls("Cut").Enabled = False
End Sub

You also can hide the menu item rather than disable it, simply set the Visible property to False:

Private Sub Worksheet_Activate()
 CommandBars("Cell").Controls("Cut").Visible = False
End Sub

Worksheet_Deactivate Event Procedure

We already learn Excel detects when a worksheet is activated and fires the Worksheet_Activate event. Similarly, the Worksheet_Deactivate event occurs when you leave the current worksheet and activate a different worksheet. In the previous example, we’ve disabled and hide Cut item form the shortcut menu, once a menu item is hidden or disabled, it is hidden or disabled for the entire workbook. To make that menu item available for other worksheets, you must enable or visible it when you leave the worksheet. See Worksheet_Deactivate event procedure example:

Private Sub Worksheet_Deactivate
 CommandBars("Cell").Controls("Cut").Enabled = True
 'CommandBars("Cell").Controls("Cut").Visible = True 
End Sub

Preventing user from leaving worksheet

The following example uses the Worksheet_Deactivate event to prevent a user from activating any other sheet in the workbook. If Sheet1 is deactivated, the user gets a message and return to Sheet1:

Private Sub Worksheet_Deactivate()
 MsgBox "Sorry, you're not allowed leave"
 Sheets("Sheet1").Activate
End Sub

Worksheet_Activate event not fires

Worksheet events do not fire when the workbook is opened. For example, the Worksheet_Activate() event for Sheet1 does not fired when the workbook is opened and Sheet1 is already activated. To check, you must select another sheet tab and select again that worksheet tab again.

Code entered in wrong module
Where did you write the code? The code will not work if it is written to another module instead of its respective module. Each worksheet has its own Activate event, and you have to add code to its module in order to trap its Activate event. You can quickly access that code window by right-clicking the worksheet’s tab and selecting the View Code:

Events are disabled
If the code was in the right worksheet module, the events may be disabled. Excel will not fire any event if the Application.EnableEvents property set to False. The Application object covers all of Excel, so disabling or enabling events will not just affect that workbook but it also affects all open workbooks.

Try closing other workbooks one by one to find which one is interfering with your code. Or, write the following code the in the immediate window and press enter button:

Application.EnableEvents = True

If events was turned off, it should work now.