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.

That’s 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.

__

Or watch on YouTube: Protect Sheets Macro Fails on Excel Sheets

_________________

2 comments to Warning For Grouped Sheets

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>