Application OnTime error

[YellowCows]

Hi everyone,

I'm relatively new to VBA and was looking for a way to repeat an action in a spreadsheet. I stumbled across the Application.OnTime procedure and thought I'd give it a go.

I tried a piece of code I want to run when a workbook is opened and closed. I got the code to run on opening the workbook and at regular 5 minute intervals but when the workbook is closed, it throws up a Run-time error ‘1004’: Method ‘OnTime’ of object’_Application’ failed. The code is posted below.

Private Sub Workbook_Open()
Application.OnTime NextUpdate, "TimRun" , , True
End Sub

Public Sub TimRun()
MsgBox "Sheet Updated!", vbOKOnly, "Hello"
NextUpdate = Now + TimeValue("00:05:00")
Application.OnTime NextUpdate, "TimRun"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime NextUpdate, "TimRun", , False
MsgBox "Are you sure you want to close this workbook?", vbYesNo, "Hello"
End Sub

Any help would be most welcome.

[Keebellah]

Change this part:

Application.Run("TimRun")

This for all instances where you have , "TimRun",

[YellowCows]

Hi Keebellah,

Thanks for the reply.

Just so I've got this right, the code for the workbook open command would look something like this:

Private Sub Workbook_Open()
Application.Run("TimRun") , , True
End Sub

Correct??

[Keebellah]

No I said the section with TimRun mhad to be corrected

All instances of AppliCation.OnTime !!!!

Private Sub Workbook_Open()
Application.OnTime NextUpdate, Application.Run("TimRun") , , True
End Sub

[YellowCows]

Whoops!! My bad.

I'll change all the instances and see how it goes.

Thanks again.

[YellowCows]

Hello,

So I updated the code to change all instances of "TimRun". I now have another problem.

When the file is opened, the message displays and when the OK button is clicked, the message immediately displays again instead of every 5 minutes as I thought it would. This happens constantly and the message doesn't disappear but just keeps running. It looks like the code is stuck in a loop and I have to kill Excel using Task Manager as I can't close the workbook normally.

Below is the amended code.

Private Sub Workbook_Open()
Application.OnTime NextUpdate, Application.Run("TimRun") , , True
End Sub

Public Sub TimRun()
MsgBox "Sheet Updated!", vbOKOnly, "Hello"
NextUpdate = Now + TimeValue("00:05:00")
Application.OnTime NextUpdate, Application.Run("TimRun")
End Sub

Help

[romperstomper]

It doesn't look like you declared NextUpdate anywhere? Assuming all your code is in the ThisWorkbook module, your code should be:

Dim NextUpdate as Date
Private Sub Workbook_Open()
TimRun
End Sub


Public Sub TimRun()
MsgBox "Sheet Updated!", vbOKOnly, "Hello"
NextUpdate = Now + TimeValue("00:05:00")
Application.OnTime NextUpdate, "TimRun"
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime NextUpdate, "TimRun", , False
MsgBox "Are you sure you want to close this workbook?", vbYesNo, "Hello"
End Sub

[Keebellah]

Good one Rory, I forget to check if the OP had indeed declared the variable, assumptions are deadly

[YellowCows]

Hi Rory and Keebellah,

Thanks for the replies. I will update the code to include the NextUpdate declaration and see how it goes.

I'm curious to know why the Workbook Open procedure doesn't state the Application.OnTime function as true i.e.

Application.OnTime NextUpdate, "TimRun" , , True

yet the Workbook BeforeClose function does

Application.OnTime NextUpdate, "TimRun" , , False

Slightly confused

[Keebellah]

The default is always True, that's the reason.
You don't have to turn in 'ON' but you do have to turn it 'OFF'
Hope this helps you.
Happy coding

[jeffreybrown]

@YellowCows,

Please use code tags when posting code!

[YellowCows]

Hi Keebellah,

Thanks for the information. I made the changes to the code but now the error which I originally posted has appeared again (Run-time error ‘1004’: Method ‘OnTime’ of object’_Application’ failed). The Workbook Open and TimRun procedures run OK but the error appears when I try and close the workbook. It looks like the Workbook BeforeClose procedure isn't working.

I have re-posted the code below.

Private Sub Workbook_Open()
TimRun
End Sub

Dim NexUpdate as Date
Public Sub TimRun()
MsgBox "Sheet Updated!", vbOKOnly, "Hello"
NextUpdate = Now + TimeValue("00:05:00")
Application.OnTime NextUpdate, "TimRun"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime NextUpdate, "TimRun", , False
MsgBox "Are you sure you want to close this workbook?", vbYesNo, "Hello"
End Sub

The Workbook Open and Workbook BeforeClose procedures are located in the This Workbook object whereas the TimRun procedure is located in a separate module. Does this matter?

jeffreybrown - apologies. I have used the code tags in this post and will do so from now on (still learning )

[Keebellah]

You should use the NexUpdate value not the Now() + etc

[YellowCows]

Hi Keebellah,

I'm a little confused about the latest reply. The Workbook BeforeClose procedure has the NextUpdate value stated. I want the update to run every 5 minutes which is why I used the Now() + in the TimRun procedure.

How will the TimRun procedure run every 5 minutes if the time period is not specified?

[Keebellah]

TimRun takes care of the NewtUpdate value

Global NexUpdate as Date
Public Sub TimRun()
MsgBox "Sheet Updated!", vbOKOnly, "Hello"
NextUpdate = Now + TimeValue("00:05:00")
Application.OnTime NextUpdate, "TimRun"
End Sub

You have to see the NextUpdate to GLOBAL not just DIM if it's not set Global it will not be recognized outside the module

And the before close, I would do it like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
if MsgBox("Are you sure you want to close this workbook?", vbYesNo, "Hello") <> vbyes then
   Cancel = True
   Exit Sub
Endif
Application.OnTime NextUpdate, "TimRun", , False
End Sub

[YellowCows]

Hi Keebellah,

Finally got around to trying the update you suggested and .......................................... it worked!!!!

I hadn't heard of the GLOBAL declaration before but it all makes sense now.

Thanks for the explanation and all your help.