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

L

leap out

Active Member
Joined
Dec 4, 2020
Messages
277
Office Version
  1. 2016
  2. 2010
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
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)
VBA Code: 
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
 
Last edited:
Upvote 0
Hi,
It's great !
but I need after close file, then should start from the beginning as I run from the first time.
 
Upvote 0
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.
VBA Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
I need procedure to my codes after click button from the second time
Replies
2
Views
44
Omar M
O
ExcelNewbie2020
  • Solved
Vba code to run subroutine in a specific time
Replies
6
Views
291
ExcelNewbie2020
ExcelNewbie2020
R
  • Question
Three-lane Stopwatch?
Replies
7
Views
282
RaymondW
R
M
  • Solved
procedure to populate message box with yes or no if I run the macro
Replies
3
Views
223
Michael M
Michael M
M
  • Solved
populate message box from the second running the macro
2 3
Replies
23
Views
757
Mussa
M

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top