PDA

View Full Version : run msgox at certain times of the day only if a specific workbook is still open



gint32
01-23-2017, 12:51 PM
Is there a way via vba to automate a popup message box when certain times of the day is reached, with being to task heavy, lets say having a macro check systems time every 15minutes or so, using a call to wait a while , like sleep

thanks in advance for any advice


Calculate
Sleep (150000) ' delay

or application.Wait(Now + TimeValue("00:00:01"))




Option Explicit

Sub ScheduleNext()
'Schedules the macro to run again in one hour

If TimeValue(Now) = TimeValue("03:40:00 AM") Then
Application.OnTime TimeValue("03:40:00 AM"), "msgboxA"
ElseIf TimeValue(Now) < TimeValue("03:40:00 AM") Then
Application.OnTime Now + TimeValue("03:42:00 AM"), "msgboxb"
' do nothing
End If
End Sub

Sub msgboxA()
msgbox " Time for to do something"
End Sub
Sub msgboxb()
msgbox " No Time to do something"
End Sub

Logit
01-23-2017, 05:30 PM
.
Paste this into a routine module:




Option Explicit


Sub shwMsg()
Application.OnTime Now + TimeValue("00:00:20"), "shwMsg" '<--- Set for 20 Seconds. 15 minutes would be "00:15:00"
MsgBox "This is your message !"
End Sub




Activate it with a CommandButton.