Application OnTime error

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.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Any help would be most welcome.

Change this part:

Please Login or Register  to view this content.

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

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:

Please Login or Register  to view this content.

Correct??

No I said the section with TimRun mhad to be corrected

All instances of AppliCation.OnTime !!!!

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Help

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

Please Login or Register  to view this content.

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

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.

Please Login or Register  to view this content.

yet the Workbook BeforeClose function does

Please Login or Register  to view this content.

Slightly confused

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

@YellowCows,

Please use code tags when posting code!

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

TimRun takes care of the NewtUpdate value

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.