VBA to make Excel sound noise when done with countdown

With help from a few nice members I was able to get my countdown timer to work well. The only thing I would like to do it to add a bell/buzzer for 10 seconds when a countdown is complete. I can get a simple ding, but not a real bell/buzzer type alert. I am running on a mac if that matters.

Toughts?
-Frank

.
This is one method ... certain there are plenty more :

Option Explicit


#If VBA7 Then
    Declare PtrSafe Function PlaySoundAPI Lib "winmm.dll" Alias "PlaySoundA" (ByVal lpszName As String, ByVal hModule As LongPtr, ByVal dwFlags As Long) As Long
    Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
#Else
    Declare Function PlaySoundAPI Lib "winmm.dll" Alias "PlaySoundA" (ByVal lpszName As String, ByVal hModule As Long, ByVal dwFlags As Long) As Long
    Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
#End If

Const SND_LOOP = &H8
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000

Const WAV_FILE_PATH_NAME As String = "C:\Windows\Media\Chimes.wav"  ' <== Change WAV file as required here.
Const SNOOZE_INTERVAL As Long = 10 'Secs  ' <== Change snooze duration as required here.

Sub StartAlarm()
    Application.OnKey " ", "MuteAlarm"
    PlaySoundAPI WAV_FILE_PATH_NAME, ByVal 0&, SND_FILENAME Or SND_ASYNC Or SND_LOOP
End Sub

Sub StopAlarm()
    Call MuteAlarm(Snooze:=False)
End Sub

Sub MuteAlarm(Optional ByVal Snooze As Boolean = True)
    KillTimer Application.hwnd, 0
    PlaySoundAPI vbNullString, 0, 0
    Application.OnKey " "
    If Snooze Then
        Application.OnKey " ", "MuteAlarm"
        SetTimer Application.hwnd, 0, SNOOZE_INTERVAL * 1000, AddressOf StartAlarm
    End If
End Sub

In your code where you have this line : If Range("d3").Value = 0 Then Exit Sub

Change to :

If Range("d3").Value = 0 Then
'Call Play Sound macro here
Exit Sub

So in this case the .wav needs to be on the computer used. Is there a way to imbed that in the workbook so others can use it?

To Logit : you post a Windows code but the OP wrote he uses a MAC …

.
Apologies ... overlooked he was running a MAC.

If by chance the code works on your MAC, the easiest thing would be to use a .WAV file already in Windows.

I am not familiar with MACs ... so ... if by chance MAC doesn't use .was files ... again I apologize.

The link I refereed to in your previous post was developed for Mac.
Within the very comprehensive dialog there must be a guide as to the addition of sound.
I have briefly run your app and it suffers from similar problems that befell the original in the link refered to.
Yours is referring back to a common 'interval' in seven cases and gets out of 'sync' (speeds up/slows down/jumps/misses periods).
This was solved in the attached examples by assigning each timer their individual 'tick' constant.

https://www.excelforum.com/excel-pro...p-timer-5.html