Setting a macro to run at a specific time

[JoeF]

I am trying to set up a macro to go off at a certain time, when I am not at my desk. I was messing around with the following
Sub timer()
TARGET = #5:39:00 PM#
Do Until Format(Time, "h:m&quot = #5:40:00 PM#
If Format(Time, "h:m&quot = Format(TARGET, "H:M&quot Then
Sheets("Sheet1&quot.Select
ActiveCell.FormulaR1C1 = "THE TIME IS NOW " & (Format(Time, "h:m&quot
Exit Do
End If
Loop
End Sub

My only problem is that the "Do until" command needs to be later than the if statement or it ends. How do I add a minute to the target time in my "Do Until" statement? It would save me from entering the time multiple times. I tried the following, but got errors.
Do Until Format(Time, "h:m&quot = (Time, "h:m&quot + TimeValue("00:01:00&quot)


By the way, I am not very experienced with VB. Is there an easier, more efficient way to do this. I kind of just came up with it by reading help menus.

 

[RickSpr]

This would be simpler, and the DoEvents call allows other processes on your machine to keep running while you're waiting.

    Target = #5:39:00 PM#
    Do Until Time() >= Target
        DoEvents
    Loop
    Sheets("Sheet1").Select
    ActiveCell.FormulaR1C1 = "THE TIME IS NOW " & (Format(Time, "h:m")

Rick Sprague

 

[JoeF]

Thanks, the macro worked great! The next thing I wanted to do was to set an input box where you enter the time the macro should run. My only problem is that I can't get the "Do Until" statement to accept the input box value as a true time value. By the way, I am testing this in excel prior to putting it in access, that is why you see cell references in the coding. Does anyone know how to get this to work?
Sub TIMERMACRO3()
'I also tried the input box with # surrounding the time value instead of quotes.
Target = InputBox( _
prompt:="Please enter the time you would like update to begin.", _
default:="9:35:00 AM&quot
Do Until Time() >= Format(Target, "h:m&quot
DoEvents
Loop
Sheets("Sheet1&quot.Select
ActiveCell.FormulaR1C1 = "THE TIME IS NOW " & Format(Time, "h:m&quot
End Sub

 

[JoeF]

Sorry, I forgot to ask this before. How do I add text that will let me end the code pre-maturely with a few key strokes?

 

[RickSpr]

This will accomplish both:

    answer = InputBox( _
             prompt:="Please enter time you would like update to begin", _
             default:="9:35:00AM")
    Target = CDate(answer)
    Abort = False
    Do Until Time() >= Target
        DoEvents
        If Abort Then Exit Do
    Loop
    If Not Abort Then
        Sheets("Sheet1").Select
        ActiveCell.FormulaR1C1 = "THE TIME IS NOW " & (Format(Time, "h:m")
    End If

Define Abort as a public Boolean variable in a standard module.

You'll have to figure out some way for the user to indicate the desire to abort the wait. This might be a Cancel command button on a small form, or perhaps a macro they might run. For the cancel button, just set Abort to True in its Click event procedure. For the macro, you can use the SetValue action, specifying Abort as the item and True as the expression.

The DoEvents statement is your key here. Each time your loop executes DoEvents, Access will check for any other work it has pending. This could include responding to the cancel button on the form, or running the macro. When that happens, Abort will become true and your loop will terminate early. Rick Sprague