Alarm Clock for multiple times

nmcgfm

New Member
Joined
Sep 4, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
.
This macro will display a message box every two hours :

VBA Code:
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
 
Upvote 0
Thanks heaps, which part to I attach to each button please? I have to buttons, Start Timer and Stop Timer. Thanks!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Thanks, that works, but the pop up appears twice. Once I click ok, it comes back up
 
Upvote 0
.
Try this version. Tested here and works smoothly as desired.

VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top