SheetActivate Event Doesn’t Fire Between Multiple Windows

SheetActivate Event Doesn’t Fire Between Multiple Windows

In VBA you use the SheetActivate event to track when a user switches from one sheet to another. Sometimes I use it to control the state of menu items that I only want available when certain sheets are active. In the workbook below, I only want the “Add Color” button enabled when the “Colors” sheet is active.

Add Color button

The SheetActivate event works fine for this, most of the time, using code similar to this:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'forces the button's getEnabled code (below) to run
g_Ribbon_Invalidate
End Sub

Public Sub cmdAddColor_getEnabled(control As IRibbonControl, ByRef returnedVal)
'the button is enabled only if "Colors" is the active sheet
returnedVal = ActiveSheet Is ThisWorkbook.Worksheets("Colors")
End Sub

Below, the Add Color button has been disabled after switching to another sheet, just like I want:

Add Color button disabled

Recently I noticed this doesn’t necessarily work if the workbook has two or more windows. In that case, switching from one window to another doesn’t trigger the SheetActivate event, even if the second window has a different active sheet than the first. Below, I’ve switched from the “Colors” sheet in one window to the “No Colors Allowed!” sheet in the second window. The SheetActivate event hasn’t fired and the button is still enabled. It’s out of sync.

Add ButtonWindow Error

I guess it makes sense that SheetActivate wouldn’t fire. After all, within each window the active sheet is still the same. (Happily, the ActiveSheet property is still updated.)

In order to keep the button in sync, add a WindowActivate event to your code. Between it and the SheetActivate code, you’ll handle moves between sheets within the same window, and between windows to a different sheet:

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
g_Ribbon_Invalidate
End Sub

Download!
You can check it out in this sample workbook.

3 thoughts on “SheetActivate Event Doesn’t Fire Between Multiple Windows

    • I use View Side by Side all the time, to compare two or more sheets. They might be in different workbooks. In the type of situation in this post, I’ve clicked View > New Window and then View Side by Side.

      I do it to compare data tables, perhaps before and after rewriting some VBA or SQL. Or when creating a formula in one sheet that refers to other sheets. Or looking at a source table and its pivot table to try to figure out what’s going on. Stuff like that.

      I’m surprised. You don’t ever look at more than one sheet at a time?

  1. Pingback: Excel Roundup 20140407 | Contextures Blog

Leave a Reply to Doug Glancy Cancel reply

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>