Workbook_BeforeClose Event (help!)

[carlos_almeida]

Hi everybody,
I'm a newbie in VBA and this is my first post in this excellent
group...

I already saw Chip's explanation about events - http://www.cpearson.com/excel/events.htm
- as well as many posts concerning this subject but haven´t yet
figured out how to implement a Workbook_BeforeClose with the Excel's
native behaviour when having multiple workbooks open.
I have a workbook were I need to do some validations before saving and
closing (hidding some sheets, protecting some cells...).
If there isn't another book open I can use Workbook_BeforeClose on the
ThisWorkbook module to do those things without problems but if I have
more workbooks open I don't really know how to do that.

Can anyone help me, please?

Thanks for your time.

 

[Chip Pearson]

The Workbook_BeforeClose event runs only for the workbook that contains the
code. It will not run when any other workbook is closed. If you need to
detect when other workbooks close, you need to use the WorkbookBeforeClose
Application event. See http://www.cpearson.com/excel/appevent.htm for
details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting LLC
www.cpearson.com
(email on the web site)



Hi everybody,
I'm a newbie in VBA and this is my first post in this excellent
group...

I already saw Chip's explanation about events -
http://www.cpearson.com/excel/events.htm
- as well as many posts concerning this subject but haven´t yet
figured out how to implement a Workbook_BeforeClose with the Excel's
native behaviour when having multiple workbooks open.
I have a workbook were I need to do some validations before saving and
closing (hidding some sheets, protecting some cells...).
If there isn't another book open I can use Workbook_BeforeClose on the
ThisWorkbook module to do those things without problems but if I have
more workbooks open I don't really know how to do that.

Can anyone help me, please?

Thanks for your time.

 

[carlos_almeida]

Hi, everybody.

Chip, many thanks for your quick answer, but I still have some
doubts...
If a user closes a specific open workbook the solution is quite
obvious but, how do I know when user is closing application (with the
X or File|Exit)?

In this case, I would have to create a loop through all open workbooks
(as Excel does) to get the answer to my question before saving/not
saving/canceling.

I already tried some solutions but they didn't work...
As well, I would like to activate workbooks one by one while answering
to that question, as Excel does.

Can you please tell me how to do that? And perhaps, could you give me
an example, so I could really understand?

My code is as follows. Thanks to Chip and to all of you for sharing
your knowledgement.

Code In a EventClass

Option Explicit
Public WithEvents App As Application

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As
Boolean)
If Cancel = True Then Exit Sub
If Wb.Saved = False And Not Wb.IsAddin Then
With Wb
If Wb.Name <> ThisWorkbook.Name Then
Application.EnableEvents = False
Select Case MsgBox(" Do you want to save changes made
to '" & _
Wb.Name & "'?", vbExclamation + vbYesNoCancel +
vbDefaultButton1, "Microsoft Excel")
Case vbCancel
Cancel = True
Application.EnableEvents = True
Exit Sub
Case vbNo
Wb.Saved = True
Case vbYes
Wb.Saved = False
End Select
End If
End With
End If
If Wb.Saved = False Then
If Wb.Name = ThisWorkbook.Name Then
CloseThisWorkbook 'Sub that makes validations. Is in
Module1.
End If
If Wb.Path <> "" Then
Wb.Save
Else: Cancel = Not Application.Dialogs(xlDialogSaveAs).Show
End If
End If
Application.EnableEvents = True
End Sub

Code in ThisWorkbook module

Option Explicit
Dim AppClass As EventClass

Private Sub Workbook_Open()
Set AppClass = New EventClass
Set AppClass.App = Application
Application.WindowState = xlMaximized
'code...
End sub

Many thanks for your time.

Carlos Almeida


Chip Pearson escreveu: