How to Set and Ring an Alarm in Excel

Usually, we set an Alarm on our Mobiles and PCs as a reminder of something in our daily lives. Just like that, in Excel, there can be some activities you wish you could ring an alarm for. But, is it possible?

Well, Yes! Although Excel lacks a default menu or Add-In to perform this, Macro VBA is our Knight in the Shining Armor. You can set an alarm that rings at the specified time – with a reminder alert. 

Using VBA code, you can also specify the alarm to ring when the specific condition is met. In addition to this, we have covered how to play the custom sound as an alarm.

Before You Begin

• Change the Windows default Beep Sound if needed. For that, open Settings > System > Sound > More sound settings. Go to the Sounds tab. Under Program Events, select Default Beep and pick a beep sound from Sounds. Hit OK. 
• If necessary, load the Developer Tab into your Excel Ribbon.

Set and Ring Alarm on Specified Time

Using the Macro given below, we will first prompt Excel to send a Dialogue box stating, “What time would you like the alarm to ring.”

Then, whatever time you mention in the box which is the present or future time of the current date, the Alarm will ring at that time.

Here, I have also mentioned the Code to ring and receive a Reminder pop-up. So, this method is best when you need to Set a reminder for the deadlines.

You can take the reference of the expiry date from your Project Trackers like Gantt Chart. 

Also, to make it easier, we will add a separate custom menu “RingAlarm” in the Home Tab. So, let’s get started!

Step 1: Copy-Paste VBA Code

On your worksheet, enter the Alt + F11 keys for the VBA window. On VBA, press the ALt + I + M shortcut key to create a new module.

Then, copy and paste any one code from below.

Ring Alarm

This code will Beep sound only just like the Alarm.  

Dim Alarm As Double
Dim Message As String
Sub RingAlarm()
Dim When As String
    If Alarm = 0 Then
        When = InputBox("What time would like the alarm to ring?")
        On Error Resume Next
        Alarm = Date + TimeValue(When)
        On Error GoTo 0
        Application.OnTime Alarm, "RingAlarm"
    Else
    Beep
Alarm = 0
    End If
End Sub

Ring Alarm and Send a Reminder

Use this code if you want to ring the alarm as well as get a reminder alert in the pop-up box.

Dim Alarm As Double
Dim Message As String
Sub RingAlarm()
Dim When As String
    If Alarm = 0 Then
        When = InputBox("What time would like the alarm to ring?")
        If When <> "" And When <> "False" Then
        Message = InputBox("Please type your Reminder Message")
        On Error Resume Next
        Alarm = Date + TimeValue(When)
        On Error GoTo 0
        Application.OnTime Alarm, "RingAlarm"
        End If
    Else
    Beep
    MsgBox Message
    Message = ""
Alarm = 0
    End If
End Sub

Step 2: Create Custom Alarm Ribbon

1. Click File tab > Options.
   
2. Go to Customize Ribbon Category.
   
3. Under the Customize the Ribbon on the right part, expand Home menu and click on New Group.
   
4. While the New group is still selected, hit the Rename button and type a Name.
   
5. Now, below Choose commands from, set the option to Macro. From the list, choose the RingAlarm Macro and hit the Add button.
   
6. Again, select the Added button and Rename Macro. You can also add the emoji if you want. I entered the name RingAlarm.
   
7. Click OK.
   

Step 3: Ring Alarm

1. On your Home Tab, click on the Custom menu you created earlier.
   
2. You’ll get a dialogue box saying “What time would you like the alarm to ring?” Enter the Time you want to ring the alarm and hit OK.
   
3. If you used the second code, type your Reminder Message and click OK.
   

You’ll hear an alarm during the set time. Also, for users who have set the reminder, you’ll receive a message pop-up too. 

Finally, to save the Macro workbook, enter Ctrl + S and pick No in the prompt. On the Save As window, expand the Save as type and pick Excel Macro-Enabled Workbook. Hit Save. 

Set and Ring Alarm When a Condition is Met

In the above method, we learned how to ring an alarm at the specified time. Be it in the current time or future time. Now, I will guide you on how to ring an Alarm when the condition is met. 

For this, we will first create a RingAlarm custom function. Then, nest IF and RingAlarm functions together to test criteria and beep the sound.

Step 1: Create Function

1. Press Alt + F11 to open the VBA window.
2. Go to Insert Tab and pick Module.
   
3. Copy and Paste the code.
   

Function RingAlarm() As String
Beep
RingAlarm = ""
End Function

4. Hit Save button.

Step 2: Nest Function and Ring Alarm

As an example, I have the Expiry Date in Column B3. I want to ring an alarm if the Expiry Date > Current time which is 11/28/2023 13:31 at the time of writing this article.

For that, I entered the given formula.

=IF(B3 > NOW(), RingAlarm(), "Not Yet")

Since it met the condition, the formula played a default system beep sound. In case the condition did not match, the IF function would’ve returned “Not Yet.”

Ring Custom Alarm Sound 

So far, we set the VBA code to ring the default beep sound of your Windows PC. But, the alarm sound is not limited to this. You can also download the Sound from online and ring it in Excel. 

1. Press down Alt + F11 for the Microsoft Visual Basic for Applications window.
2. To add a Module, enter Alt + I + M.
3. Copy this code and paste it into the Module.

#If Win64 Then
    Private Declare PtrSafe Function PlaySound Lib "winmm.dll" _
        Alias "PlaySoundA" (ByVal lpszName As String, _
        ByVal hModule As LongPtr, ByVal dwFlags As Long) As Boolean
#Else
    Private Declare Function PlaySound Lib "winmm.dll" _
        Alias "PlaySoundA" (ByVal lpszName As String, _
        ByVal hModule As Long, ByVal dwFlags As Long) As Boolean
#End If
Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
Function MyAlarmSound() As String
Call PlaySound("Your Sound File Path Name", _
0, SND_ASYNC Or SND_FILENAME)
    MyAlarmSound = ""
End Function

4. Now, on your File Explorer, locate the downloaded sound. Right-click on the File and choose Copy as Path. Paste the file path in “Your Sound File Path Name” in the code. For example, here, my path is "c:\users\nisha\downloads\mixkit-battleship-alarm-1001.wav"
   
5. Save the Code in VBA. On your sheet, enter =MyAlarmSound in a cell to play that sound. Or, nest the function with others like we did above.