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.