Alarm Clock for multiple times

[nmcgfm]

Hi, im new here and I am still quite new when it comes to macros and vba, so any help would be greatly appreciated. I am trying to make a command button that will that will start an alarm clock to go off at certain times. Its a bit hard to explain, so ill give an example. So Press the button and it tells the alarm to "activate', first alarm would be at 10 am, then 12pm, 2pm, 4pm and 6pm, 10pm, 12am, 2am, 4am and 6am. Ideally I would like to be able to have another button to cancel the rest of the alarms. Im hoping this is possible and someone can help me. Thanks in advance

 

[Logit]

.
This macro will display a message box every two hours :

Dim TimeToRun

'Sub auto_open()
'    Call ScheduleclrCol                        'uncomment this for code to auto run when sheet is opened
'End Sub

Sub ScheduleclrCol()
    TimeToRun = Now + TimeValue("02:00:00")     '<----- adjust time to fire here  HH:MM:SS
    Application.OnTime TimeToRun, "aLarm"
End Sub

Sub aLarm()
    Calculate
    MsgBox "2 HR ALARM NOTICE", vbInformation, "ALARM"
    Call ScheduleclrCol
End Sub

'Sub auto_close()                               'uncomment this for code to auto stop when sheet is closed
'    On Error Resume Next
'    Application.OnTime TimeToRun, "aLarm, , False"
'End Sub

Sub manual_stop()                               'button click for stop (Manual Stop)
    On Error Resume Next
    Application.OnTime TimeToRun, "aLarm, , False"
End Sub

 

[nmcgfm]

Thanks heaps, which part to I attach to each button please? I have to buttons, Start Timer and Stop Timer. Thanks!

 

[Logit]

STOP = manual_stop

START = ScheduleclrCol

 

[nmcgfm]

Sorry, I am quite confused. I don't think it works, I want to press the button, which will start the alarm clock, so in 2 hours a pop up will appear saying Check in required.

 

[Logit]

Paste two command buttons on the sheet. When you paste the first button a small window appears showing you the various macros that are included in
your workbook. Select SCHEDULECLRCOL ... then change the title to that Command Button to START.

Paste the second button and select the macro MANUAL_STOP ... then change the title to that Command Button to STOP.

 

[nmcgfm]

Thanks, that works, but the pop up appears twice. Once I click ok, it comes back up

 

[Logit]

.
Try this version. Tested here and works smoothly as desired.

Option Explicit

Dim DownTime As Date

Sub GoTimer()
    DownTime = Now + TimeValue("00:00:10")  ''<--- change time to close here
    Application.OnTime EarliestTime:=DownTime, Procedure:="ShowMessage", Schedule:=True
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=DownTime, _
      Procedure:="ShowMessage", Schedule:=False
    MsgBox "Timer Stopped"
 End Sub
 
Sub ShowMessage()
    MsgBox "2 Hour Notice"
    GoTimer
End Sub

As before ... two buttons. One connected to GoTimer the other to StopTimer.

 

[nmcgfm]

Thank you so much for your help. 1 more thing, Can I get a pop up box for when the timer is started as well, Like the Timer Stopped one.

 

[Logit]

Yes ... I could do that BUT you wouldn't be learning VBA which is very important.

Let me guide you and see how you do.

Create a separate macro (name it anything you want).

In that macro, borrowing from the existing line of code that has MsgBox in it, place the same line in the macro.
That is the first thing you want to see when you start the timer, so that should be the first line of code in your macro.

After seeing the MsgBox the next thing you want is to start the timer so ... to "call" another macro you simply enter
the macro name. Put that below the MsgBox line of code.

Now ... you'll want to change the macro connection to the CommandButton on your sheet to the next macro name.

See what you come up with.