On 29 nov, 22:39, mcolson <mcolson1...@gmail.com> wrote:
> On Nov 29, 12:08*pm, alf <laurentb...@gmail.com> wrote:
>
>
>
> > On 29 nov, 18:25, mcolson <mcolson1...@gmail.com> wrote:
>
> > > On Nov 28, 1:07*pm, JLGWhiz <JLGW...@discussions.microsoft.com> wrote:
>
> > > > 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" wrote:
> > > > > I would like to have an event that runs when a workbook is closedif
> > > > > the save button is pressed. *I was trying to run the macro witha
> > > > > Workbook_BeforeClose event, but this wasn't working for me. *Ifthe
> > > > > 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.
>
> > > 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
>
> 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
|
Similar Threads
