Updated Macro to warn user when multiple sheets are selected

[NeoM6]

Hi all,

So far I have this code to run when multiple sheets are selected, but I want this to run everytime >1 sheets are selected, and that doesn't seem to be automatically happening when I select two sheetse. Any idea on how to automate this?


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

 

[mole999]

I would think it needs to be a workbook event, don't know what would be optimal

 

[JoeMo]

This will alert if more than one sheet is selected whenever the workbook its installed in is activated. Is that what you want?

This is a Thisworkbook module.
To install the code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and double-click the 'Thisworkbook' icon.
3. Copy the code below from your browser window and paste it into the white space in the VBE window.
4. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
5. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
If Wn.SelectedSheets.Count > 1 Then
    MsgBox "WARNING! " & Wn.SelectedSheets.Count & " sheets are grouped", _
    vbOKOnly + vbCritical, "Grouped Sheets"
End If
End Sub

 

[NeoM6]

Yep, that's what I want! Still not running though - perhaps it's because other VBA codes are installed? I have certain large service packages installed (FactSet, for example)

 

[JoeMo]

Yep, that's what I want! Still not running though - perhaps it's because other VBA codes are installed? I have certain large service packages installed (FactSet, for example)

Will only run when you leave the window for another window in excel and then return to the window with multiple sheets selected - assuming you have installed the code correctly.

 

[NeoM6]

ah, there it goes. Thank you!