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

 

[hippiehacker]

this should work

Copy these macros to a module

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

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

In the WorkBook_Open event call the ShowMsg macro.

 

[bamaisgreat]

this should work

Copy these macros to a module

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

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

In the WorkBook_Open event call the ShowMsg macro.

This is not working in the open event

Private Sub Workbook_Open()
 Call ShowMsg
 End Sub

could you show me what im doing wrong ? It says Ambigious
I already have one in the open event, this is what it looks like

Private Sub Workbook_Open()
 Call RunOnTime
 End Sub
Private Sub Workbook_Open()
 Call ShowMsg
 End Sub

 

[bamaisgreat]

I tried this in the open event

Private Sub Workbook_Open()
 Call RunOnTime
 Call ShowMsg
 End Sub

It didnt throw up am ambigious error, but it didnt work either ???????

 

[hippiehacker]

of course its not working i assume you have other reginal setting as we have in europe for this you have to change the timeformat from 15:30 to i guess 3:30pm or something like this

you can do a

debug.print time()

to see your timeformat my result would be 17:21:45 by now

 

[bamaisgreat]

of course its not working i assume you have other reginal setting as we have in europe for this you have to change the timeformat from 15:30 to i guess 3:30pm or something like this

you can do a

debug.print time()

to see your timeformat my result would be 17:21:45 by now

I dont have a clue how to do this, this is what i tried but it does not work

Sub ShowMsg()
If TimeValue(Now()) > "09:57:00" Then
Exit Sub
End If
Application.OnTime Now + TimeValue "09:57:00" , "TimedMsgBox"
End Sub
Sub TimedMsgBox()
If TimeValue(Now()) = "09:57:00" Then
MsgBox "Send Form Before your shift ends"
End If
ShowMsg
End Sub
Also were do you put the AM or PM

 

[bamaisgreat]

Im stumped. Ive put this in but when the time comes it does nothing>>>
I have this in This Workbook

Private Sub Workbook_Open()
Call RunOnTime
Call ShowMsg
End Sub

I have this in a module

Sub ShowMsg()
If TimeValue(Now()) > "08:24:10 AM" Then
Exit Sub
End If
Application.OnTime Now + TimeValue("00:00:01"), "TimedMsgBox"
End Sub
Sub TimedMsgBox()
If TimeValue(Now()) = "08:22:00 AM" Then
MsgBox "Send Form Before your shift ends"
End If
ShowMsg
End Sub

 

[hippiehacker]

run

Sub tvalue()
MsgBox TimeValue(Now())
End Sub

what are you getting?

 

[bamaisgreat]

run

sub tvalue()
msgbox timevalue(now())
end sub

what are you getting?

9:45:27 am

 

[hippiehacker]

in your code try with small am instead of AM

If TimeValue(Now()) > "08:24:10 [COLOR=#ff0000]am[/COLOR]" Then

If TimeValue(Now()) = "08:22:00 [COLOR=#ff0000]am[/COLOR]" Then

would this work?