VBA macro to pop up alerts at specific times of the Day

Kopynwa · Jan 31st 2020

Hello everyone

Please can anyone help me make this code work? I want the macro to pop up alert at specific times during the day. At the moment when I hit F5 it runs but on it's own it doesn't run at the set times. The module I used is 'This Workbook'. Any help to tweak this will be much appreciated. Thanks

Code

Sub MessageTime1()
Application.OnTime TimeValue("11:00:00"), "ShowMessageMidday"
End Sub

Sub ShowMessageMidday()
MsgBox "Mid-day Statement run starts in an hour"
End Sub

Sub MessageTime2()
Application.OnTime TimeValue("13:00:00"), "ShowMessage_afternoon"
End Sub

Sub ShowMessage_afternoon()
MsgBox "Afternoon Recs in 30min"
End Sub

Sub MessageTime3()
Application.OnTime TimeValue("14:00:00"), "ShowMessage_settle"
End Sub

Sub ShowMessage_settle()
MsgBox "Review settlement starts in an hour"
End Sub

Sub MessageTime4()
Application.OnTime TimeValue("16:35:00"), "ShowMessage_EOD"
End Sub

Sub ShowMessage_EOD()
MsgBox "EOD in starts in an hour"
End Sub

Display More

**Jack in the UK** · Feb 23rd 2020

The code should be located in "ThisWorkbook" and under "Workbook" and "Open"

Something like

Code

Private Sub Workbook_Open()
Application.OnTime TimeValue("11:00:00"), "ShowMessageMidday"
End Sub

jiuk

**Jack in the UK** · Feb 23rd 2020

Code

Option Explicit
'// jiuk - Add this code In a the Worksheet "ThisWorkbook"

Private Sub Workbook_Open()
    myCodes_To_RUN
    
'// jiuk - any code you require can be added
theEnd:

'// jiuk do not Exit sub here

End Sub


'// jiuk - Add this code In a the Worksheet "Public "Module
'// jiuk - This adds the scheduling times for the codes to run

Sub myCodes_To_RUN()

    Select Case Time
        Case Is < TimeSerial(11, 0, 0)
            Application.OnTime TimeValue("11:00:00"), "ShowMessageMidday"
        Case Is < TimeSerial(12, 25, 0)
            Application.OnTime TimeValue("12:25:00"), "ShowMessage_afternoon"
        Case Is < TimeSerial(15, 25, 0)
            Application.OnTime TimeValue("15:25:00"), "ShowMessage_settle"
        Case Is < TimeSerial(18, 15, 0)
            Application.OnTime TimeValue("18:15:00"), "ShowMessage_EOD"
    End Select
    
theEnd:

'// jiuk do not Exit sub here

End Sub

End Sub

'// jiuk - Add theses codes in a the Standard Module

Sub ShowMessageMidday()
MsgBox "Mid-day Statement run starts in an hour"

theEnd:
Exit Sub
End Sub

Sub ShowMessage_afternoon()
MsgBox "Afternoon Recs in 30min"

theEnd:
Exit Sub
End Sub

Sub ShowMessage_settle()
MsgBox "Review settlement starts in an hour"

theEnd:
Exit Sub
End Sub

Sub ShowMessage_EOD()
MsgBox "EOD in starts in an hour"

theEnd:
Exit Sub
End Sub

Display More

VBA macro to pop up alerts at specific times of the Day

Participate now!

Share

Tags