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
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")
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:
You can check it out in this sample workbook.