Code that waits 60 seconds then msgbox appears. 1

[Navvy]

Hello all,

I'm looking for some code that waits for 60secs then after the 60 secs a msgbox appears.

I have the following but I'm not sure if it is useful:

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 5
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

Thank you.

 

[Chance1234]

Wait Method Example

This example pauses a running macro until 6:23 P.M. today.

Application.Wait "18:23:00"
This example pauses a running macro for approximately 10 seconds.

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
This example displays a message indicating whether 10 seconds have passed.

If Application.Wait(Now + TimeValue("0:00:10&quot) Then
MsgBox "Time expired"
End If

http://WWW.5YLAC.S5.COM

 

[Navvy]

Thanks for the reply but I seem to be getting a compile error.

Ideally what I want is a prompt box which asks the user "Have You finished?" If the user hits "yes" then the workbook will save and exit. If the user hits "no" it will loop and wait another 60 secs.

 

[xlbo]

Try this

sub TimeTest
tryAgain:
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 60
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
myRep = msgbox("Have you finished",vbyesno)
If myRep = vbno then
goto tryAgain
else
activeworkbook.save
activeworkbook.close
end if
end sub

 

[Navvy]

Waits 60 secs - thx. The problem is it seems to hang. The 60 seconds is a window for the user to type some data into one of the cells.

So, after 60 secs the Box will appear and allow the user to decide if he has finished or needs more time to write the data.

At the moment the application hangs and I'm unable to write anything.

 

[kevinclark]

Another method that may be of interest is the OnTime method. it is a timer method that can be coded to fire an event at a preset time or at intervals. Main difference between OnTime and Wait is that the OnTime method will not interfere with running code / acts like a actual timer.

Sub MyTimer()
Application.OnTime When:=Now + TimeValue("00:00:60&quot, _
Name:="Project1.Module1.PromptUser"
End Sub

Sub PromptUser()
PromptUser = Msgbox("Quit ?",vbyesNo)
if PromptUser = VBYes then
End 'stops all running code
activeWorkbook.save
activeWorkbook.close
Else
MyTimer 'restarts the timer
End If
End Sub

 

[kevinclark]

if my sample gives you errors try this

Sub MyTimer()
Application.OnTime Now + TimeValue("00:00:60&quot, "PromptUser"
End Sub

Sub PromptUser()
Dim PrmptUser As Long
PrmptUser = MsgBox("Quit ?", vbYesNo)
If PrmptUser = vbYes Then
End 'stops all running code
ActiveWorkbook.Save
ActiveWorkbook.Close
Else
MyTimer 'restarts the timer
End If
End Sub

 

[Navvy]

Kevin, seems like your method will work, i.e. allow the user to make some input rather than let the application hang.

But I seem to be getting an error on

Application.OnTime Now + TimeValue("00:00:60&quot, "PromptUser"


Error="Type Mismatch"

 

[kevinclark]

Possibly a spelling error

Double check the spelling in your OnTime method matches the spelling of the routine you are calling.

Application.OnTime Now + TimeValue("00:00:60&quot, "PromptUser"


Sub PromptUser()

 

[Navvy]

Kevin - found the problem. It was the timing issue. It doesn't recognise "00:00:60". Needs to be "00:01:00"

Thanks for your help, very useful feature.