Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeClose event and OnTime method
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeClose event and OnTime method
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeClose event and OnTime method
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 "Jim Cone" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook_BeforeClose event and OnTime method
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" 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 "Jim Cone" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook_BeforeClose event is not canceled | Excel Programming | |||
Detecting a cancel on Workbook_BeforeClose event? | Excel Programming | |||
Workbook_Beforeclose vs BeforeClose Event | Excel Programming | |||
Workbook_BeforeClose Event | Excel Programming | |||
OnTime event not firing in Workbook_Open event procedure | Excel Programming |