We’ve all been there. I group a few sheets, change several things at once, and pat myself on the back for being so efficient. A few changes later, I realize that the sheets are still grouped and that I’m an idiot. I finally decided to do something about it and I happened on this old post from Contextures. Debra asks
What would you like Excel to do, to make grouped sheets more noticeable?
I’d like a warning, but I’d like it to be non-modal. That is, I don’t want it to interrupt me.
What about a hideous Ribbon tab that appears when you group sheets?
First, I used the CustomUI editor to add some XML to my workbook.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="GroupAlertRibbonLoad"> <ribbon startFromScratch="false" > <tabs> <tab id="tabGroupAlert" label="Grouped Sheets Warning" getVisible="tabGroupAlert_GetVisible" > <group id="grpGroupAlert" label="Grouped Sheets Warning" > <button id="GA1" size="large" imageMso="ColorRed" /> <button id="GA2" size="large" imageMso="ColorYellow" /> <button id="GA3" size="large" imageMso="ColorRed" /> <button id="GA4" size="large" imageMso="ColorYellow" /> <button id="GA5" size="large" imageMso="ColorRed" /> <button id="GA6" size="large" imageMso="ColorYellow" /> <button id="GA7" size="large" imageMso="ColorRed" /> <button id="GA8" size="large" imageMso="ColorYellow" /> <button id="GA11" size="large" imageMso="ColorRed" /> <button id="GA12" size="large" imageMso="ColorYellow" /> <button id="GA13" size="large" imageMso="ColorRed" /> <button id="GA14" size="large" imageMso="ColorYellow" /> <button id="GA15" size="large" imageMso="ColorRed" /> <button id="GA16" size="large" imageMso="ColorYellow" /> <button id="GA17" size="large" imageMso="ColorRed" /> <button id="GA18" size="large" imageMso="ColorYellow" /> <button id="GA19" size="large" imageMso="ColorRed" /> <button id="GA20" size="large" imageMso="ColorYellow" /> <button id="GA21" size="large" imageMso="ColorRed" /> <button id="GA22" size="large" imageMso="ColorYellow" /> <button id="GA23" size="large" imageMso="ColorRed" /> <button id="GA24" size="large" imageMso="ColorYellow" /> <button id="GA25" size="large" imageMso="ColorRed" /> <button id="GA26" size="large" imageMso="ColorYellow" /> </group> </tab> </tabs> </ribbon> </customUI> |
It’s a couple dozen buttons with alternating colors. It appears whenever a sheet is activated and sheets are grouped. Here’s the code in a standard module
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Public goRibbon As IRibbonUI Private Const msMODULE As String = "MCallbacks()" 'Callback for customUI.onLoad Sub GroupAlertRibbonLoad(ribbon As IRibbonUI) Set goRibbon = ribbon End Sub 'Callback for tabGroupAlert getVisible Sub tabGroupAlert_GetVisible(control As IRibbonControl, ByRef returnedVal) If Not ActiveWindow Is Nothing Then returnedVal = ActiveWindow.SelectedSheets.Count > 1 If returnedVal Then goRibbon.ActivateTab "tabGroupAlert" End If End If End Sub |
The onLoad procedure sets up a global Ribbon variable. The getVisible procedure controls whether you can see the custom tab. If the count of SelectedSheets is greater than one, returnedVal is set to True and that makes the tab visible. The If block shows the tab if it’s visible using the ActivateTab method.
In the ThisWorkbook module:
|
1 2 3 4 5 |
Private Sub Workbook_SheetActivate(ByVal Sh As Object) goRibbon.Invalidate End Sub |
When a sheet is activated, the Ribbon is invalidated and the getVisible procedure is forced to run again.
The next step would be to put this code in an add-in with a class module and application level events to monitor all workbooks.
You can download GroupSheetAlert.zip
“I realize that the sheets are still grouped and that I’m an idiot.” Ah yes. Nice idea.
Great idea, but maybe not quite such painfully eye-stabbing colors… ;-)
I just wish I had a nickel for every time…..
And if you’re lucky, a macro hasn’t wiped out your undo stack in the meantime.
“I realize that the sheets are still grouped and that I’m an idiot” – could not have described that feeling any better.
Given you are a keyboard guy, the ribbon is just a piece of decoration – but for the rest of us who use the ribbon, having the grouped sheets warning tab would only work until we move to another tab in the process of getting stuff done. And the problem is remembering after a while, not immediately after. What about putting a flashing button on the QAT – then it is always visible
The tab is shown every time you switch sheets. So when you first group, the tab is shown. Then if you go to another spot on the ribbon, and switch sheets, the tab is shown again. If you don’t switch sheets, then you could still do some damage. You could use the SelectionChange event to show it whenever you move anywhere – same sheet or different. That might be a little much, though.
I thought about a callback to change the button images, making them appear to flash by alternating red and yellow. What would drive the callback though? A timer? That would just slow everything down.
I should go to every macro in my PMW and put a line at the top