VBA: Message Box pop up at end of timer ???

[bamaisgreat]

The code below is what I use to shut excel down at a certain time.
I would like to have some code that at say 3:30pm a message box will pop up that says "Send Form Before your shift ends"

Thanks as Always

Sub RunOnTime()
dTime = TimeSerial(16, 31, 2)
Application.OnTime dTime, "QuitExcel"
End Sub
Sub QuitExcel()
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.Quit
End Sub
Sub CancelOnTime()
Application.OnTime dTime, "RunOnTime", , False
End Sub

 

[bamaisgreat]

It didnt change anything >> It still does nothing when time comes around

 

[bamaisgreat]

VBA Guys can you look at this ??? Im stumped.......

 

[hippiehacker]

It didnt change anything >> It still does nothing when time comes around

I told you you should change two lines

try this code

Sub ShowMsg() 
If TimeValue(Now()) > TimeValue("3:30:00 am") Then 
Exit Sub 
End If 
Application.OnTime Now + TimeValue("00:00:01"), "TimedMsgBox" 
End Sub  

Sub TimedMsgBox() 
If TimeValue(Now()) = TimeValue("3:30:00 am") Then 
MsgBox "Send Form Before your shift ends" 
End If 
ShowMsg 
End Sub

 

[bamaisgreat]

Thanks that was it. Leaving the 0 off the front of the time

 

[hippiehacker]

its always be tricky if you have other regional settings time/dates/currency but we figured out

 

[bamaisgreat]

I wish I new more about VBA. one more question. Can the be modified to pop up the message a 2 different times??? Say 4:50 PM and then again at 9:00 PM

 

[hippiehacker]

I wish I new more about VBA. one more question. Can the be modified to pop up the message a 2 different times??? Say 4:50 PM and then again at 9:00 PM

sure try this

Sub ShowMsg()
If TimeValue(Now()) > TimeValue("9:10:00 pm") Then
Exit Sub
End If
Application.OnTime Now + TimeValue("00:00:01"), "TimedMsgBox"
End Sub

Sub TimedMsgBox()
If TimeValue(Now()) = TimeValue("4:50:00 pm") Then
MsgBox "Send Form Before your shift ends pt1"
End If
If TimeValue(Now()) = TimeValue("9:00:00 pm") Then
MsgBox "Send Form Before your shift ends pt2"
End If
ShowMsg
End Sub

 

[hippiehacker]

I wish I new more about VBA. one more question. Can the be modified to pop up the message a 2 different times??? Say 4:50 PM and then again at 9:00 PM

sure try this

Sub ShowMsg()
If TimeValue(Now()) > TimeValue("9:10:00 pm") Then
Exit Sub
End If
Application.OnTime Now + TimeValue("00:00:01"), "TimedMsgBox"
End Sub

Sub TimedMsgBox()
If TimeValue(Now()) = TimeValue("4:50:00 pm") Then
MsgBox "Send Form Before your shift ends pt1"
End If
If TimeValue(Now()) = TimeValue("9:00:00 pm") Then
MsgBox "Send Form Before your shift ends pt2"
End If
ShowMsg
End Sub