Grouped Tab Alert

[Guest]

hi there


i am looking to make an alert that pops up when a file is opened and any of
the tabs are 'grouped'.

I realise this may seem a bit stupid bearing in mind the filename at the top
clearly goes to "filename.xls [Grouped]" in such an event, but i really need
to ram it down peoples throats if some crazy fool has saved a file with
grouped tabs.

So, i am currently thinking i need some sort of 'Private Sub
Workbook_Open()' with an If function that says "if any of the tabs are
grouped (preferably without having to specifically name all the tabs & all
their possible grouping permutations!) show a 'MsgBox' with a 'vbOKonly' to
warn them, otherwise, let it open as normal".

I am struggling to program the initial part of the If that looks to see if
Sheets are laid out in an Array (which i am using currently as VB's code for
the term "Grouped").

Does anyone have any ideas?

I suppose i could also use a working version of such a code to issue a
strongly worded reprimand to someone attempting to save the file while tabs
are grouped with a 'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As
Boolean, Cancel As Boolean)'?

As always, any help gratefully received, let me know if you need any more info

cheers

jb

 

[Norman Jones]

Hi JB,

Try something like:

'=============>>
Private Sub Workbook_Open()
If ThisWorkbook.Windows(1).SelectedSheets.Count > 1 Then
'do something, e.g
MsgBox ThisWorkbook.Windows(1).SelectedSheets.Count _
& " Sheets are grouprd"
End If
End Sub
'<<=============

 

[Guest]

If ActiveWindow.Selectedsheets.count > 1 then


or

if thisworkbook.Windows(1).SelectedSheets.count > 1 then

 

[Guest]

Cheers Tom & Norman, both worked well. the VB formula was much more like an
Excel formula than i was expecting, which gives me encouragement!!!

If ActiveWindow.Selectedsheets.count > 1 then


or

if thisworkbook.Windows(1).SelectedSheets.count > 1 then

--
Regards,
Tom Ogilvy

hi there


i am looking to make an alert that pops up when a file is opened and any of
the tabs are 'grouped'.

I realise this may seem a bit stupid bearing in mind the filename at the top
clearly goes to "filename.xls [Grouped]" in such an event, but i really need
to ram it down peoples throats if some crazy fool has saved a file with
grouped tabs.

So, i am currently thinking i need some sort of 'Private Sub
Workbook_Open()' with an If function that says "if any of the tabs are
grouped (preferably without having to specifically name all the tabs & all
their possible grouping permutations!) show a 'MsgBox' with a 'vbOKonly' to
warn them, otherwise, let it open as normal".

I am struggling to program the initial part of the If that looks to see if
Sheets are laid out in an Array (which i am using currently as VB's code for
the term "Grouped").

Does anyone have any ideas?

I suppose i could also use a working version of such a code to issue a
strongly worded reprimand to someone attempting to save the file while tabs
are grouped with a 'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As
Boolean, Cancel As Boolean)'?

As always, any help gratefully received, let me know if you need any more info

cheers

jb