Grouped Sheets Warning

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.

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

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:

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

6 thoughts on “Grouped Sheets Warning

  1. “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

  2. 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

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.