[SOLVED] How refresh selection with OnTime? Error: Cannot run the macro

How refresh selection with OnTime? Error: Cannot run the macro

The cell refreshes when I run the macro the first time

Sub RefreshAction()
    Range("b7").Select
    Application.Run "RefreshCurrentSelection"
    Application.OnTime (Now() + TimeValue("00:00:05")), "thisworkbook.Action"
End Sub

I get an error message immediately after

Cannot run the macro "C\Desktop\XYZ.xlsm'!thisworkbook.Action'. The macro may not be available in this workbook or all macros may be disabled.

I have gone through "Trust Center->Trust Center Settings->Macro Settings->Enable all macros.

The "Trust access to VBA project object model" box is also clicked.

Solution

See the absolute reference for more details : CPearson OnTime

First issue, you need to store the time that you'll input in your OnTime method to be able to stop it. (Here I declared a Public TimeToRun As Date)

Second Point To use the OnTime method continuously, you need to reset the timer at the end of your timed procedure (here RefreshAllStaticData).

So your whole code should look like this :

Public TimeToRun As Date 'so that TimeToRun can be used in both the functions

Sub RefreshAction()
    Range("b7").Select
    Application.Run "RefreshCurrentSelection"
    DoEvents
    'Store the next date of execution in TimeToRun
    TimeToRun = Now() + TimeValue("00:00:05")
    'Launch the next OnTime
    Application.OnTime TimeToRun, "RefreshAllStaticData"
End Sub


Sub RefreshAllStaticData()

'--++-- Place your code here, as it is now --++--

'----Call RefreshAction to reset the OnTime method
'---------to another 5 seconds and keep "looping"
RefreshAction

End Sub


Sub Kill_OnTime()
'Launch this to stop the OnTime method
Application.OnTime _
    earliesttime:=TimeToRun, _
    procedure:="RefreshAllStaticData", _
    schedule:=False
End Sub