close workbook event

[mcolson]

I would like to have an event that runs when a workbook is closed if
the save button is pressed. I was trying to run the macro with a
Workbook_BeforeClose event, but this wasn't working for me. If the
user hits cancel, the macro still runs. I would be fine with that if
I could easily reverse the action because the cancel button was
pressed. Does anyone have any suggestions. Basicially, the macro
should run when actually closing the workbook. If cancel is pressed,
it should not run. I tried using the workbook deactivate event, but
the macro was running if I switched to another workbook.

 

[JLGWhiz]

Did you have Cancel = True in your event macro? If Cancel is set to true by
the macro, then when the user presses cancel, the close operation is
terminated.

 

[mcolson]

Did you have Cancel = True in your event macro?  If Cancel is set to true by
the macro, then when the user presses cancel, the close operation is
terminated.

No, I do not have any conditions that set cancel = true. Cancel is
set to true though automatically via Microsoft if the user presses
cancel. I debugged the code by stepping through it and noticed the
code is ran before the user has the chose of clicking cancel.

 

[alf]

No, I do not have any conditions that set cancel = true.  Cancel is
set to true though automatically via Microsoft if the user presses
cancel.  I debugged the code by stepping through it and noticed the
code is ran before the user has the chose of clicking cancel.

The last event fired is Workbook_deactivate.
You must put your code on it.
In order to know that you quit excel, you must set a boolean to True
in the Before_Close Event
then test it in the workbook_deactivate event

 

[mcolson]

The last event fired is Workbook_deactivate.
You must put your code on it.
In order to know that you quit excel, you must set a boolean to True
in the Before_Close Event
then test it in the workbook_deactivate event

I tried this. When I open a new workbook, the macro ends up running
on the new workbook.

 

[alf]

I tried this.  When I open a new workbook, the macro ends up running
on the new workbook.

Try this (excel 2003).

ThisWorkbook Section:
---------------------------------
Private Sub Workbook_BeforeClose(cancel As Boolean)
WB_CLOSE
End Sub

Private Sub Workbook_Deactivate()
WB_DEACTIVATE
End Sub

In a Module:
-----------------
Dim bQuit As Boolean

Sub WB_CLOSE()
bQuit = True
End Sub

Sub WB_DEACTIVATE()
If bQuit = True Then
'Excel quit!
'Put your code for excel quit only
MsgBox "excel quit"
Else
'Put your code for deactivate event only
MsgBox "deactivate event"
End If
End Sub

-----
But there is a problem : :
* If you click cancel when you want to exit, bQuit is set to True,
but if you cancel you want bQuit = False...

You could reset bQuit to false in others events (workbook_activate,
worksheet_activate, worksheet_deactivate, selection_change), but if
just after cancellation, you switch to another workbook, the
workbook_deactivate is executed with bQuit = True...

To avoid this, you can try "ontime" excel/vba function to reset bQuit
to false a few seconds after the execution of the Before_Close Event.

And the code modified would be:

ThisWorkbook Section:
---------------------------------
Private Sub Workbook_BeforeClose(cancel As Boolean)
WB_CLOSE
End Sub

Private Sub Workbook_Deactivate()
WB_DEACTIVATE
End Sub

In a Module:
-----------------
Dim bQuit As Boolean

Sub WB_CLOSE()
Dim dTime As Date
bQuit = True
'Then reset bQuit in 2 seconds
dTime = Now + TimeValue("00:00:02")
Application.OnTime dTime, "ResetQuit"
End Sub

Sub WB_DEACTIVATE()
If bQuit = True Then
'Excel quit!
'Put your code for excel quit only
MsgBox "excel quit"
Else
'Put your code for deactivate event only
MsgBox "deactivate event"
End If
End Sub

Private Sub ResetQuit()
'MsgBox "reset bQuit"
bQuit = False
End Sub

-----
Try it, this example works fine for me.
I hope ou understand my explanations (I'm french, and sometimes it is
difficult to explain things in english)

Best regards