I have a workbook in which I use an ontime event in order to update a value in a cell. However, it seems I am doing something wrong with ending the event, as the workbook reopens after I close it. I'll admit that I am more of a dabbler in VBA than a super-user, and for the most part the code in the book has been borrowed/stolen from various forums and webpages. I.e. I don't understand it all that well, so I hoped someone here please could show me where I've gone wrong.
Here's the code in "ThisWorkbook":
[vba]Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime setDate, "TimeUp", , False
MsgBox (setDate)
End Sub
Private Sub Workbook_Open()
UpdateTime
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
UpdateTime
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
On Error Resume Next
Application.OnTime setDate, "TimeUp", , False
End Sub
[/vba] And in the module:
[vba]Option Explicit
Public setDate As Date
Sub UpdateTime()
setDate = Now() + TimeValue("00:01:00")
Application.OnTime setDate, "TimeUp"
End Sub
Sub TimeUp()
ThisWorkbook.Worksheets("ArbPlan 2011 (5)").Range("Y29") = Time
UpdateTime
End Sub
[/vba] I'm using Excel 2003, if that makes any difference.
I might be missing it, but I don't see anything that should actually fall over. That said, I am wondering if using the workbook deactivate/activate events would serve you better.
In a junk workbook, try the following:
In the ThisWorkbook Module:
[VBA]Option Explicit
Private bolIsClosing As Boolean
Private Sub Workbook_Activate()
UpdateTime
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
bolIsClosing = True
End Sub
Private Sub Workbook_Deactivate()
KillOnTime
If bolIsClosing Then
MsgBox setDate
End If
End Sub[/VBA]
In a Standard Module:
[VBA]Option Explicit
Public setDate As Date
Sub UpdateTime()
setDate = Now() + TimeValue("00:00:05")
Application.OnTime setDate, "TimeUp"
End Sub
Sub KillOnTime()
On Error Resume Next
Application.OnTime setDate, "TimeUp", , False
On Error GoTo 0
End Sub[/VBA]
In short, the workbook activate/deactivate events are called when the workbook is opened/closed, so we can skip using the Open event. The we can just set a flag (bolIsClosing) to catch our MsgBox if the user is closing.
I'll give it a shot when I get to work tomorrow. The messagebox was just to see if the code was run, and then I forgot to remove it before copying it here. I think some of the problem might be that the workbook is opened from another one, so there might be several ontime-events running. Would have posted that code here too, but since it isn't I who has written that and I am a bit unsure if it is supposed to be a strictly internal document I was hesitant too. Will reconsider if this doesn't solve the problem.
Hmm. So it still reopens the workbook, even with the new code - I have no idea why the ontime event isn't killed :P Probably has something to do with there being several events running as I mentioned earlier.
However I've figured out a workaround to the most immediate problem, which was that the automated process collecting info from the sheets kept getting more workbooks opened. Since this process is always run from the same user, I just added in some code that made VBA skip the events if it is this user accessing the data. So unless someone finds it interesting to try to dig down to the actual problem, I think I'll just let the human users deal with a somewhat buggy sheet, and save myself further headache.
Unabashedly, there are others here who are quite a bit more skilled, so hopefully someone fitting that description jumps in. That said, I belileve that if you are interested in pursuing a better solution, we'll need to see a decently accurate example that replicates the issue.
You are of course, most welcome for what little (actually nothing, as we have not solved anything yet) help I gave.
The effort counts Unless someone says they're particularly interested in getting to the bottom of the problem, I think I'll say I'm happyish with how it works now.
I am, however, attaching one of the workbooks in which the code is used (in case anyone is interested). The problem seems to crop up if I open (and close?) more than one of these workbooks before the previous ontime event has timed out.
Reply With Quote
