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.
The SheetActivate event works fine for this, most of the time, using code similar to this:
'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:
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.
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:
g_Ribbon_Invalidate
End Sub
Download!
You can check it out in this sample workbook.
I’ve never use Windows. You obviously find them useful…when would you use ’em, Doug?
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?
Pingback: Excel Roundup 20140407 | Contextures Blog