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

 

[CG5]

...and no message box saying hello

 

[smozgur]

It is really strange. Unlikely but I wonder if it is related with your computer (like wrong time settings), or something related with that particular workbook.

Can you try the same Intermediate window verification with a brand new workbook, same standard module with the macro contains only MsgBox function? I know it is frustrating to debug it, but I am out of ideas at this point, so trying to minimize all the other effects and make sure that Application.Ontime is actually working in your computer or not - for some reason. I don't think that you can upload your file somewhere for us to test it, so this is the only way that I can debug remotely.

 

[CG5]

Thanks - I can try it this out and get back...thanks for your patience!

 

[CG5]

OK - so I tried a brand new spreadsheet, with no others open, and wrote the following code, and the box did not appear. The time on my machine reconciles....can't understand what's goin on.

Private Sub Workbook_Open()
Application.OnTime TimeValue("13:33:20"), "Macro1"
End Sub


Sub Macro1()
MsgBox ("Hello")
End Sub

 

[smozgur]

If in this new workbook, as I tried to explain the debugging steps, the following code doesn't trigger the macro when you execute it in the Immediate window:

Application.OnTime now(), "FileName.xlsm!Macro1"

Or even following without saving the new workbook:

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

In this case, unfortunately all I can say is the OnTime method in the Excel installation in your computer doesn't work for some reason that I don't understand either. I am not asking to try the workbook in another computer, because I do the exact same thing, and it is working for me as it is supposed to be.
Perhaps someone else comes up with a possible solution or diagnose method.

 

[CG5]

Thanks for all your help - much appreciated. It seems that application.ontime simply isn't working, and given I run trading software on my machine, I am inclined not to try and tinker with my system too much. I could, I expect, come up with a solution where I read a cell from my spreadsheet that, when it turns to a 1, could trigger a macro...and create a time based function in there. Any ideas how I could write that?

Thanks again!

 

[smozgur]

You can use Worksheet_Change event procedure, but the cell's value should be changed manually, this even is not triggered if the cell has a formula, and it's result changes to 1.

Something like this (the codes goes to the Sheet1 class object, where I assumed the cell will be there to check if its value 1 or not):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("A1").Address Then
        If Range("A1").Value = 1 Then
            Call Macro1
        End If
    End If
End Sub

 

[CG5]

Thank you!