Microsoft Excel includes several built-in tools that allow users to automate conditional formatting (like changing the color of a cell) depending on whether specific conditions are met.
But many people don’t realize it’s possible to make Excel play a sound using Microsoft’s Visual Basic for Applications (VBA). This Excel tutorial will explain how to sound an alarm in Excel when a condition is met.
How to Ring an Alarm in Excel
Two steps are involved in getting Excel to play a sound based on a particular trigger. First, you must add custom code enabling Excel to play the sound. Then, you need to tell Excel what event or value will trigger the sound.
To do this, you must create a new rule using an Excel VBA macro. This sounds complicated, but don’t worry—all you need to do is copy and paste the code below and then change some basic parameters.
How to Create an Alarm in Excel
- Open your Excel spreadsheet.
- Press Alt + F11 to open the pop-up Microsoft Visual Basic for Applications window.
- Click Insert > Module.
- Enter the following code:
Function MakeABeep() as String
Beep
MakeABeep = “”
End Function
- Click Save.
- In the Save as Type drop-down menu, select Excel Macro-Enabled Workbook.
- In any cell, type “=MakeABeep()” and press Enter. If you hear a system sound, your code has worked, and it’s time to move on to the next step.
How to Trigger the Alarm in Excel
Next, you need to add conditional formatting to the cell you would like to play a sound upon a specific event. This will depend on what you want the trigger to be, so you might need to know how to use the various Excel formulas to make it work exactly as planned.
Here’s a basic example showing how to get cell A1 to play a sound if it reaches 100:
- Select an empty cell in your Excel worksheet.
- Type “=IF(A1>100, MakeABeep(),””)” and press Enter.
When the A1 cell value reaches 101, a system sound will play.
How to Get Excel to Play a Custom Sound
You can get Excel to play multiple custom sounds rather than relying on the single system sound. This way, you can set up multiple alarms to ring when different conditions are met.
- Open your Excel file.
- Press Alt + F11 to open Microsoft VBA.
- Press Insert > Module.
- In the dialog box, type:
#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 AlarmSound() As String
Call PlaySound(“C:UsersUserDownloadssound.wav”, _
0, SND_ASYNC Or SND_FILENAME)
AlarmSound = “”
End Function
- Replace “C:UsersUserDownloadssound.wav” with the location of the sound file that you would like to use. To get this, open the folder in File Explorer, right-click the address bar, and select Copy address as text.
- Press Ctrl + S to save the file.
- In the Save as Type drop-down list, select Excel Macro-Enabled Workbook.
- In any blank cell, add your conditional formatting equation. For example, “=IF(A1>100, AlarmSound(),””)”. As above, this will cause an alarm to play if the A1 cell reaches the value of 100.
Note: In this template code, “AlarmSound” was used instead of “MakeABeep.” Both of these can be written as whatever you would like. For example, if you wanted multiple alarms to ring for different conditions, you could use “Alarm1,” “Alarm2,” and so on.
What Kind of Alarms Can You Create in Excel?
Excel is a powerful tool, and it includes several formulas you can use to format your data exactly how you want it. To use these rules, click the Home tab and click Conditional Formatting.
Here are a few examples of data conditions that you can tell Excel to play an alarm for:
- If the value is equal to, greater than, lesser than, or between another value(s)
- If the cell is a duplicate
- If the cell refers to a particular date (like a due date or expiration date)
- If the value is in a specific percentile (for example, the top 10% or above average)
- If the cell is a particular color—useful when you have previous conditional formatting rules set up
Data Management Has Never Been Easier
Microsoft Office’s Excel is one of the most powerful data validation and analysis tools on the market and has been for a long time. Whether you want your Excel workbook to display your data in an easy-to-understand manner or format cells to notify you when conditions are met, Excel has the tools for you.
Source by helpdeskgeek.com
