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"
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