Grouped Tab Alert

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
'<<=============
 
If ActiveWindow.Selectedsheets.count > 1 then


or

if thisworkbook.Windows(1).SelectedSheets.count > 1 then
 
Cheers Tom & Norman, both worked well. the VB formula was much more like an
Excel formula than i was expecting, which gives me encouragement!!!



Tom Ogilvy said:
If ActiveWindow.Selectedsheets.count > 1 then


or

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

--
Regards,
Tom Ogilvy



JB2010 said:
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
Click to expand...
Click to expand...
 
Back
Top