Application.OnTime TimeValue - Run Once only

I have the following code which works great BUT I only want the macro to run once. However, if the worksheet is accessed multiple times during a shift at work the "MyMacro" is run however many times the worksheet has been selected.

Is there an easy way so the macro is only run once at the designated time? Unsure if it is relevant but the file will be saved in a "teams" location.

Any support appreciated, running the macro as the file is open may not be an option so if we can ignore that for now that would be great.

Many thanks in advance, Alan.


Private Sub Worksheet_Activate()

    Application.OnTime TimeValue("20:50:12"), "MyMacro"

End Sub


Sub MyMacro()
    Dim rtn As Integer
    rtn = MsgBox("Good to Go!", vbOKOnly + vbInformation, "GTG")
End Sub

Solution

I think there are a couple of ways of going about this. One is to ensure that the Application.OnTime 'timer' runs only once. The other would be for MyMacro to store the time that it runs and then, if run again within a short time period, just exit.

This answer focuses on the former which I think is much cleaner. The code is

On Error Resume Next
Application.OnTime TimeValue("20:50:12"), "MyMacro", Schedule:=False
On Error GoTo 0
Application.OnTime TimeValue("20:50:12"), "MyMacro"

Passing False for Schedule in the first call of Application.OnTime means that any previously-set 'timer' for the same procedure and time will be deleted but this does result in an error if there is no existing matching 'timer' hence the use of error handling.

Obviously the subsequent call of Application.OnTime then sets (or re-sets) the 'timer'.

You can read more about Application.OnTime and the Schedule parameter in the MS Docs at Application.OnTime.