Run Macro at Specific Time

[CG5]

Hi - I am trying to get a macro to run automatically at a specific time, while the workbook is open.

I have gone into Microsoft Excel objects > ThisWorkbook and typed in:

Private Sub Workbook_Open()
Application.OnTime TimeValue("11:00:00"), "Macro_Name"
End Sub

..but it fails to work. I am sure this is an easy fix...hoping someone can help
Thanks
C

 

[smozgur]

Some questions that would help to find out what's wrong with the code if any.

• Is Macro_Name located in a standard module in the same workbook as a Public procedure?
• Do you expect it to run at 11:00AM or 11:00PM. The current code will work at AM.
• Does it cause any error?

 

[CG5]

Thanks - Macro_Name is located in a standard module (Module 1), just beneath the ThisWorkbook menu listing in the project window. 11am is the test time, thanks for clarifying. No errors, just does nothing. However, when I click the "Run" button in the VB editor, it runs nicely...just not triggering automatically.

 

[smozgur]

Interesting. It should work.

You have a visual sign that you can see the macro is working, right?
And I assume the macros are enabled for the workbook already.

What happens if you set it to 1 minute later from now, and save & close & re-open the workbook?

Edit: Or what happens if you execute the following in the Immediate (Debug) window?

Application.OnTime now(), "Macro_Name"

 

[CG5]

Not sure what is meant by a visual sign the macro is working - when I manually trigger it, it is obvious it is doing some work, but while I am waiting for this code to automatically trigger, I am not seeing any visual indicators.

Yes - all enabled...not even sure how to disable, never done that before.

When I run the code you suggest in the immediate window, it brings up a macro selector box rather than running the macro - and I have checked that the spelling is the same, and it checks out...does this give you any ideas?

 

[smozgur]

When I run the code you suggest in the immediate window, it brings up a macro selector box rather than running the macro - and I have checked that the spelling is the same, and it checks out...does this give you any ideas?

One more test please. What happens if you execute the following in the Immediate window?
(Please make sure there is only one workbook is opened, or at least Thisworkbook class of the actual workbook is selected in the Project window to make sure using the correct workbook's name in the code below.)

Application.OnTime now(), Thisworkbook.Name & "!Macro_Name"

 

[CG5]

Thanks - to clarify the code, what exactly am I subbing in to "Thisworkbook.Name"?

The spreadsheet is called DYNAMIC_V4.xlsb...

 

[smozgur]

Not sure what is meant by a visual sign the macro is working - when I manually trigger it, it is obvious it is doing some work, but while I am waiting for this code to automatically trigger, I am not seeing any visual indicators.

I was just trying to make sure if the macro is being called, but somehow it exits without making necessary actions due to a condition or for a reason.

That's why I always use a simple MsgBox "hello" command line to make sure if the macro is being called when I debug the implementation.

 

[smozgur]

Thanks - to clarify the code, what exactly am I subbing in to "Thisworkbook.Name"?

Just copy and paste it in the immediate window. I used ThisWorkbook object to avoid writing the file name manually.

Or, you can also use the actual file name:

Application.OnTime now(), "DYNAMIC_V4.xlsb!Macro_Name"

And please put a MsgBox "hello" line at the beginning of the Macro_Name sub procedure before testing. You can delete it later.

 

[CG5]

Did these steps precisely as you suggested - no change....when I run in immediate, I get the macro selector box again...