Workbook_BeforeClose event and OnTime method

[Bejaz]

I've got a workbook that should only be closed if the value of a certain cell
is not equal to 2. I've been trying to use the BeforeClose event and an
OnTime method to do it. My code in the ThisWorkbook module is:


Private Sub Workbook_BeforeClose(Cancel as Boolean)
If (Worksheets("Sheet1").Range("F2").Value = 2) Then
Application.OnTime Now() + TimeValue("00:00:05"), "TryToClose", _
Schedule:=True
Cancel = True
End If
End Sub


If cell F2 has a value of 2, the macro cancels the save, waits five seconds,
and calls TryToClose.


Sub TryToClose()
ThisWorkbook.Close
End Sub


TryToClose then attempts to close the workbook (which activates the
Workbook_BeforeClose event again). This should continue with one macro
calling the other (and vice versa) until the value of F2 is no longer 2.
However, the loop only works once. It appears that when TryToClose calls the
BeforeClose event, the OnTime method does not work properly. I'm not sure
why.

Any help would be very much appreciated.

 

[Jim Cone]

B,

If the spreadsheet is not updated the code would continue running,
so I put in an exit after about 30 seconds.
'------------------------------
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim lngCount As Long
Do While Worksheets("Sheet1").Range("F2").Value = 2
DoEvents
Application.Wait Now + 0.000055
lngCount = lngCount + 1
If lngCount > 5 Then
Cancel = True
MsgBox "Time expired"
Exit Do
End If
Loop
End Sub
'------------------------------
Jim Cone
San Francisco, USA


"Bejaz" <[email protected]>
wrote in message
I've got a workbook that should only be closed if the value of a certain cell
is not equal to 2. I've been trying to use the BeforeClose event and an
OnTime method to do it. My code in the ThisWorkbook module is:

Private Sub Workbook_BeforeClose(Cancel as Boolean)
If (Worksheets("Sheet1").Range("F2").Value = 2) Then
Application.OnTime Now() + TimeValue("00:00:05"), "TryToClose", _
Schedule:=True
Cancel = True
End If
End Sub

If cell F2 has a value of 2, the macro cancels the save, waits five seconds,
and calls TryToClose.

Sub TryToClose()
ThisWorkbook.Close
End Sub

TryToClose then attempts to close the workbook (which activates the
Workbook_BeforeClose event again). This should continue with one macro
calling the other (and vice versa) until the value of F2 is no longer 2.
However, the loop only works once. It appears that when TryToClose calls the
BeforeClose event, the OnTime method does not work properly. I'm not sure
why.

Any help would be very much appreciated.

 

[Bejaz]

That doesn't really help me with the problem I'm having. The Wait method
suspends all Excel activity, so the value won't be able to become 2. The
gist of my problem is that the BeforeClose event won't keep running a macro
that (essentially) calls the Workbook_BeforeClose macro.

 

[Jim Cone]

Bejaz,

I was able to change the value of F2 while my code was running.
The "DoEvents" function yields execution so that the operating
system can process it.
However, I would personally never use code like I provided to you.
It would be much better to simply check the value of F2 in the
before close event and present a MsgBox to the user if the
value is incorrect.

Jim Cone
San Francisco, USA



"Bejaz" <[email protected]>
wrote in message
That doesn't really help me with the problem I'm having. The Wait method
suspends all Excel activity, so the value won't be able to become 2. The
gist of my problem is that the BeforeClose event won't keep running a macro
that (essentially) calls the Workbook_BeforeClose macro.
Bejaz