Is it possible to prompt an alert upon exit of a worksheet alerting the user
that the worksheets are being saved with grouping on?
Situation:
I have an Excel file that has multiple worksheets within it that is updated
with grouping on to make changes to multiple worksheets within this file at
the same time; occassionally i have users that forget to ungroup those
selected sheets and the next person in that file does not notice and then
their data is entered into all the worksheets causing a great deal of
confusion.

Please help!

In Thisworkbook module...............

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveWindow.SelectedSheets.Count 1 Then
MsgBox "Ungroup sheets before closing"
Cancel = True
End If
End Sub


Gord Dibben MS Excel MVP

On Wed, 25 Jul 2007 15:22:01 -0700, Holly
wrote:

Is it possible to prompt an alert upon exit of a worksheet alerting the user
that the worksheets are being saved with grouping on?
Situation:
I have an Excel file that has multiple worksheets within it that is updated
with grouping on to make changes to multiple worksheets within this file at
the same time; occassionally i have users that forget to ungroup those
selected sheets and the next person in that file does not notice and then
their data is entered into all the worksheets causing a great deal of
confusion.

Please help!

Is it possible to prompt an alert upon exit of a worksheet alerting the user
that the worksheets are being saved with grouping on?

Yes. If sheets are grouped, the vba code below will alert you when exiting.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveWindow.SelectedSheets.Count 1 Then
a = MsgBox("Worksheets are grouped. Continue exiting? (If No,
remember to resave after ungrouping.)", vbYesNo)
If a = vbNo Then Cancel = True
End If
End Sub

To install, copy the code, bring up the Visual Basic editor (Alt + F11),
doubleclick "ThisWorkbook" under your file's VBAProject, paste, and save.

- David

Have you thought about putting the same kind of routine in the workbook_open or
auto_open procedures.

Then you don't have to bother the person closing the workbook with any prompt or
a reminder to save.

Option Explicit
Sub auto_open()
ActiveSheet.Select
End Sub

Holly wrote:

Is it possible to prompt an alert upon exit of a worksheet alerting the user
that the worksheets are being saved with grouping on?
Situation:
I have an Excel file that has multiple worksheets within it that is updated
with grouping on to make changes to multiple worksheets within this file at
the same time; occassionally i have users that forget to ungroup those
selected sheets and the next person in that file does not notice and then
their data is entered into all the worksheets causing a great deal of
confusion.

Please help!

--

Dave Peterson

There's Dave again.

Starting from the beginning instead of at the rear end like those of us who just
see the words OP posted and comply with that.

Much easier on users your way.


Gord

On Wed, 25 Jul 2007 21:11:48 -0500, Dave Peterson
wrote:

Have you thought about putting the same kind of routine in the workbook_open or
auto_open procedures.

Then you don't have to bother the person closing the workbook with any prompt or
a reminder to save.

Option Explicit
Sub auto_open()
ActiveSheet.Select
End Sub

Holly wrote:

Is it possible to prompt an alert upon exit of a worksheet alerting the user
that the worksheets are being saved with grouping on?
Situation:
I have an Excel file that has multiple worksheets within it that is updated
with grouping on to make changes to multiple worksheets within this file at
the same time; occassionally i have users that forget to ungroup those
selected sheets and the next person in that file does not notice and then
their data is entered into all the worksheets causing a great deal of
confusion.

Please help!

Dave, why does your sub work when added to a regular module, but not
to"ThisWorkbook"?

- David

Dave Peterson wrote:
Have you thought about putting the same kind of routine in the workbook_open or
auto_open procedures.

Then you don't have to bother the person closing the workbook with any prompt or
a reminder to save.

Option Explicit
Sub auto_open()
ActiveSheet.Select
End Sub

Holly wrote:
Is it possible to prompt an alert upon exit of a worksheet alerting the user
that the worksheets are being saved with grouping on?

Auto_open in a general module is the key. Excel knows to look at for that name
(along with auto_close) and do something special.

If you want the same kind of thing under ThisWorkbook, you have to play be
excel's rules and use the _beforeclose (or _beforesave???) event.

It's kind of like Autoexec.bat when you turn on the pc--well, when you used DOS
a hundred years ago!

David Hilberg wrote:

Dave, why does your sub work when added to a regular module, but not
to"ThisWorkbook"?

- David

Dave Peterson wrote:
Have you thought about putting the same kind of routine in the workbook_open or
auto_open procedures.

Then you don't have to bother the person closing the workbook with any prompt or
a reminder to save.

Option Explicit
Sub auto_open()
ActiveSheet.Select
End Sub

Holly wrote:
Is it possible to prompt an alert upon exit of a worksheet alerting the user
that the worksheets are being saved with grouping on?

--

Dave Peterson