Warning For Grouped Sheets

Warning For Grouped Sheets

If you select more than one sheet in a workbook, and start typing or formatting, that data or formatting will be entered in all the selected sheets, not just the active sheet.

Sheet grouping is a great feature – if you want to type on all the sheets, or add formatting, or whatever. But it’s not so great if you don’t notice that the sheets are grouped, and accidentally work on all the sheets.

It’s easy to forget that you grouped a few sheets – you interrupt your work for a quick phone call, or grab another cup of coffee, and poof! That memory is gone, and the worksheet damage can begin.

Highlight Grouped Sheets

In all the years that I’ve used Excel, the grouped sheets alert hasn’t changed. Excel gives us big, flashy warnings about other things, but not for grouped sheets.

Instead, we only get subtle reminders:

  • a tiny “[Group]” at the end of the file name, in the title bar.
  • bold text on the selected sheet tabs

And that text in the title bar isn’t highlighted in green, like it is in my screen shot below. Nope, it’s just plain text, and very easy to overlook.

sheetsgrouped02

What would you like Excel to do, to make grouped sheets more noticeable?

  • Change the title bar to purple?
  • Play scary music?
  • Show a scrolling message?

Show a Warning

Those features might be in the next version, and in the meantime, you can use a macro that counts the number of sheets that are selected. If the count is higher than 1, the macro shows a warning message, so you will remember to ungroup the sheets.

sheetsgrouped

To use this code, paste it into a regular code module in your Excel file. Then, you could run the code at the start of other macros, or add it to the Workbook_SheetActivate event code.

[Code updated – thanks to Dick Kusleika]

Sub GroupedCount()
Dim wdw As Window
Dim ws As Worksheet
On Error Resume Next
Set wdw = ActiveWindow
Dim Count As Long
Count = wdw.SelectedSheets.Count
If Count > 1 Then
   MsgBox "WARNING! " & Count & " sheets are grouped", _
      vbOKOnly + vbCritical, "Grouped Sheets"
End If
End Sub

Video: Macro Fails If Sheets Grouped

Grouped sheets can also cause problems if you try to run code that only works on ungrouped sheets. For example, if code protects or unprotects all the sheets, the code will fail if the sheets are grouped, as you can see in this video.

To prevent errors, you can ungroup the sheets before running the macro, or add a line of code in the macro, to select just one sheet.

_________________

4 thoughts on “Warning For Grouped Sheets”

  1. Thanks for the reminder.
    Yes, the default grouped sheets behavior suffers from the same problem as hidden rows/columns – the effect of the user action is invisible until you next unhide the rows/columns and realize you unintentially copy/pasted over a whole lot of work.
    I think there should be a warning for both hidden sheets and hidden rows/columns when pasting, formatting, editing, anything that changes hidden objects. The warning could be toggled on/off in options with a default of on (my experience bias is I rarely hide or group so I’m more apt to be bit by this behavior).

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.