Grouping

[Holly]

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!

 

[Gord Dibben]

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

 

[David Hilberg]

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

 

[Dave Peterson]

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

 

[Gord Dibben]

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

 

[David Hilberg]

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

- David

 

[Dave Peterson]

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!