Macro will not launch using Application.OnTime TimeValue

[Josh Colton]

[Solved]
I am using the function: Application.OnTime TimeValue to run a specific macro every day at 4:00 PM, but at 4:00 PM the macro tries to launch another macro but is disabled by Excel even though the macro security is set to allow it to run. If I run the “Sample_Macro_Name” manually, it runs just fine. However, as soon as I use the another macro to launch the “Sample_Macro_Name” at a specific time, when the that time occurs, instead it returns the error message:

"Cannot run the macro Sample_Macro_Name() The macro may not be available in this workbook or all macros may be disabled”

Excel version: Office 2010
Macro security: set to enable all macros to be run.
Workbook is saved as a macro enabled workbook in a “trusted file location” on my local drive.

I have tried placing the 2nd bit of code, the macro, in standard modules, worksheet modules, and ThisWorkbook module, but in every case the macro will not fire with the Application.OnTime function, but will always fire when I manually launch it. It is as if Microsoft does not allow timed macros to fire off at a certain time due to security, yet it can be manually launched.

Please Login or Register  to view this content.

Folks, I appreciate your wise guidance.

[dangelor]

Josh,

Works OK on my 2010 system. Possibly, you have a corrupted workbook?

[protonLeah]

Question: That last "double quote": (”) after the macro name has a code value of 148, while a (") has a code value of 34; where did that (”) come from?
The VB editor puts a regular " at the end of the line so that other symbol may be seen as a part of the macro name but it is missing from the actual macro; also, the () in the calling line are not needed.

[Leith Ross]

Hello Josh,

Welcome to the Forum!

You did not say if the second macro is in the same workbook. If it is not then the other workbook must be open and you must qualify the macro name with the name of the workbook it is in. Application.OnTime will not run if the workbook is not open.

[Josh Colton]

test.xlsm

Had to take a break for a few hours out of frustration. Thanks guys. It is good to know that this running on another Excel 2010 system.

I have uploaded a test worksheet for which you can see the code. The prior code was copied from the original into my post and edited a bit to change the macro names which is why I had the quotation mark wrong.

I tried a re-boot of my system.
I created a fresh workbook (attached)
I disabled my anti-virus temporarily to test (to rule that out)

I still get the same error.

[Leith Ross]

Hello Josh,

Try this version of the macro. Generally, macros that are for general use are placed in standard VBA modules. This macro resides in the "Sheet1" module so you must qualify the call.

Please Login or Register  to view this content.

[protonLeah]

This one triggers the ontime event and runs ok:
(I moved the macro_name.. to a standard module from the sheet module.

[Josh Colton]

Woo Hoo! Thanks, resolved. It was a naming issue. I put () after the macro name, and I notice you folks did not. So it was not finding it.
Really appreciate it.



Now, if I want the macro to run every day at 4:00 PM, and not just the first time the workbook opens, will it automatically do that, or will I need to put some kind of loop on it so it runs each day at 4:00?

[Leith Ross]

Hello Josh,

Application.OnTime by default schedules the macro the run. This means as long as the workbook is open, the macro will be run again at the same time.

[Josh Colton]

Thanks all. [Solved] --- not sure how to mark solved from the interface.

[OnErrorGoto0]

I confess I was under the impression that OnTime will only run once as it assumes today's date if you do not specify a date. Unless your called code reschedules it, the code will only be run once, I believe. If this is incorrect can someone please let me know? (easier than me testing it over 24 hours)