How I run my code at specific time without interfere from me

[leap out]

Hi

I would like to run my code automatically at time PM17:00 . when I try to click button 1 so should show message how many remaining time to running the code . and when reach to the PM17:00 then should run the code automatially without click button 1 .

thanks

 

[Artik]

Provided that after defining the time to run the macro, you do not close Excel for a moment (but you can temporarily close the file from which you ran the code). There is a button in the worksheet, which is NOT an ActiveX button, to which the InitializeMacro macro is assigned.
Code in a standard module (e.g. Module1)

Option Explicit

Dim RunAt As Variant
Dim InProgress As Boolean

Sub InitializeMacro()
   
    If IsEmpty(RunAt) Then
        If Time > TimeSerial(17, 0, 0) Then
            If MsgBox("It's already past 5 p.m. Do you want to run MyMacro after all?", vbQuestion + vbYesNo) = vbYes Then
                RunAt = Now
            Else
                Exit Sub
            End If
        Else
            RunAt = TimeSerial(17, 0, 0)
            MsgBox "Countdown has started", vbInformation
        End If
       
        InProgress = True
        Application.OnTime RunAt, "MyMacro"
       
    ElseIf InProgress Then
        MsgBox "There is " & Format(RunAt - Time, "hh:mm:ss") & " left to run MyMacro.", vbInformation
       
    End If
   
End Sub


Sub MyMacro()
    InProgress = False
    RunAt = Empty
   
    MsgBox "Hello"
End Sub

Artik

 

[leap out]

Hi,
It's great !
but I need after close file, then should start from the beginning as I run from the first time.

 

[Artik]

If you want to stop the countdown after initiating the macro, you have two choices:
1. In addition to closing the workbook you also close the Excel application. Then the scheduled task will be reset. In this case, you do not need to change anything in the code, just remember to close the application. But this is an inconvenient way, because the user has to remember to perform a specific action.
2 You need to reset the task programmatically. And we will do it in the "old style" in the Auto_Close procedure.
In the logic of the previous code, I made a mistake. If the countdown was initiated, the file was closed (but not Excel) before the designated time, and then reopened, then the public variables RunAt and InProgress have already been reset, and you can initiate another task to execute at the designated time (5 p.m.). So it may happen that MyMacro executes twice, which is obviously not desirable. Since your needs are somewhat different, the code will not need to anticipate the above-described behavior, because (at least that's how I understand your new needs) the moment the workbook is closed, the countdown is to be reset. That is, the moment you close the file before 5 p.m., MyMacro will not run.
Below is the new full code after the changes.

Option Explicit

Dim RunAt As Variant
Dim InProgress As Boolean

Sub InitializeMacro()
    Dim DefTime As Date
    
    DefTime = TimeSerial(12, 47, 0)
   'Stop
    If IsEmpty(RunAt) Then
        If Time > DefTime Then
            If MsgBox("It's already past " & Format(DefTime, "hh:mm:ss") & ". Do you want to run MyMacro after all?", vbQuestion + vbYesNo) = vbYes Then
                RunAt = Now
            Else
                InProgress = False
                RunAt = Empty
                
                Exit Sub
            End If
        Else
            RunAt = DefTime
        End If
       
        InProgress = True
        Application.OnTime RunAt, "MyMacro"
        
        MsgBox "Countdown has started", vbInformation
       
    ElseIf InProgress Then
        MsgBox "There is " & Format(RunAt - Time, "hh:mm:ss") & " left to run MyMacro.", vbInformation
    End If
   
End Sub


Sub Auto_Close()
    
    On Error Resume Next
    'reset of initiated task
    Application.OnTime RunAt, "MyMacro", , False
    'If the workbook was closed after MyMacro was executed or
    'the countdown procedure has not been initialized,
    'then the error handler will ignore the task reset
    
    
    'cleaning these variables is not necessary,
    'cause the file is closed, so the variables will reset themselves.
    RunAt = Empty
    InProgress = False
End Sub


Sub MyMacro()
    InProgress = False
    RunAt = Empty
   
    MsgBox "Hello"
End Sub

Artik

 

[leap out]

thanks again
I see if past specific time then will not show for left time
it will run the macro or not by message, but what I want if I past time also should show me left time until reach to spicific time. I hope this point is clear for you.

 

[Artik]

Unfortunately, I don't understand the problem. Please give specific examples of what you expect. Instead of general time statements, use specific times. Let the hour of execution of the macro still be 5 p.m. What should happen before 5 p.m. and what should happen after that hour?

Artik

 

[leap out]

What should happen before 5 p.m

as you did it .

and what should happen after that hour?

until reach to 5 p.m for next day when calculate from 5 p.m a today should be 24 hour
ex: when maro is 5 p.m and PC is 6:00 p.m then should show 23:00:00

when maro is 5 p.m and PC is 6:30 p.m then should show 22:30:00.
I hope this help

 

[Artik]

Next version

Option Explicit

Dim RunAt As Variant
Dim InProgress As Boolean

Sub InitializeMacro()
    Dim DefTime As Date

    DefTime = TimeSerial(11, 30, 0)

    If IsEmpty(RunAt) Then
        If Time > DefTime Then
            RunAt = Date + 1 + DefTime
        Else
            RunAt = DefTime
        End If

        InProgress = True
        Application.OnTime RunAt, "MyMacro"

        MsgBox "Countdown has started", vbInformation

    ElseIf InProgress Then
        MsgBox "There is " & Format(RunAt - Time, "hh:mm:ss") & " left to run MyMacro.", vbInformation
    End If

End Sub


Sub Auto_Close()

    On Error Resume Next
    'reset of initiated task
    Application.OnTime RunAt, "MyMacro", , False
    'If the workbook was closed after MyMacro was executed or
    'the countdown procedure has not been initialized,
    'then the error handler will ignore the task reset


    'cleaning these variables is not necessary,
    'cause the file is closed, so the variables will reset themselves.
    RunAt = Empty
    InProgress = False
End Sub


Sub MyMacro()
    InProgress = False
    RunAt = Empty

    MsgBox "Hello"

    'if you want to automatically define a new task for the next day
    'then uncomment the bottom line:
    'Application.OnTime Now, "InitializeMacro"
End Sub

Artik

 

[leap out]

great!

MsgBox "Countdown has started", vbInformation

this will be from the first time when the file is open , but my qustion after save and clode is there way without need starting from the first time when open the file every time?

 

[Artik]

Due to language problems, I don't fully understand what you mean.
For now, it seems to me that you need action according to the following scenario:
- you open a file and at that moment a task is automatically defined to be executed at 5 p.m. If you opened the file before 5 p.m., the task will execute on the same day. If you open the file after 5 p.m. the task will execute the next day provided the file is not closed by then.
- When the file is already open, pressing the button will display the time remaining to run the task.
- when you close the file, the defined task will be automatically reset.
It follows from the above description that the file must be open all the time for the defined task to execute (whether it applies to the same day or the next day).

Artik