Using Countdown in Status Bar to Play Sound

[Full Monty]

Running Excel 97:

Currently using a Countdown (shown in the Excel tips) from 180 to 0 i
th status bar as a timer.

Would like Excel to automatically play a corresponding WAV file onc
the countdown reaches certain intervals. (30, 10, 0)

Each of the three intervals listed would have their own WAV file.

I am just learning VBA and this site has been very helpful thus far
But I am stuck at this point and don't know how to proceed.

Thanks for the help

 

[Chip Pearson]

You can use a Windows API function to play a WAV file. E.g.,

Declare Function sndPlaySound32 Lib "winmm.dll" Alias _
"sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long

Sub PlaySound(FileName As String)
sndPlaySound32 FileName, 0
End Sub

Call this procedure with code like

PlaySound "C:\MyWavFile.wav"



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Bob Phillips]

Monty,

Here's some code to play a WAV file

Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000

Private Declare Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" _
(ByVal lpszName As String, _
ByVal hModule As Long, _
ByVal dwFlags As Long) As Long


Sub PlayWAVFile(Optional Async As Boolean = True)
Dim WavFile As String
WavFile = "chimes.wav"
WavFile = "C:\Windows\Media\" & WavFile
If Async Then
Call PlaySound(WavFile, 0&, SND_ASYNC Or SND_FILENAME)
Else
Call PlaySound(WavFile, 0&, SND_SYNC Or SND_FILENAME)
End If
End Sub

If you need to know how to insert it in your code, we will need to see that

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Full Monty]

Thanks for the response guys! Again since I am fairly new to VBA code,
am having trouble understanding where you want me to insert it.

The Countdown code I am currently using is:
Sub CountDown()
Dim intCounter As Integer
Dim bln As Boolean
bln = Application.DisplayStatusBar
Application.DisplayStatusBar = True
For intCounter = 180 To 1 Step -1
Application.StatusBar = intCounter & " Seconds..."
Application.Wait Now + TimeSerial(0, 0, 1)
Next intCounter
Application.StatusBar = False
Application.DisplayStatusBar = bln
End Sub

Do I insert the code you guys posted somewhere inside this code? In th
same module but separate section? Or in a new module?

I have all three WAV files located in a folder called "My Folder" in C
drive. Pathway is C:\My Folder\Sound 1" and so on.

Again thanks for all the help! I am learning alot

 

[Bob Phillips]

Here's the full monty (sorry, couldn't avoid it)

Option Explicit

Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000

Private Declare Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" _
(ByVal lpszName As String, _
ByVal hModule As Long, _
ByVal dwFlags As Long) As Long

Sub CountDown()
Dim intCounter As Integer
Dim bln As Boolean
bln = Application.DisplayStatusBar
Application.DisplayStatusBar = True
For intCounter = 180 To 1 Step -1
If intCounter = 30 Or intCounter = 10 Then
PlayWAVFile "C:\Windows\Media\chimes.wav"
End If
Application.StatusBar = intCounter & " Seconds..."
Application.Wait Now + TimeSerial(0, 0, 1)
Next
PlayWAVFile "C:\Windows\Media\tada.wav"
Application.StatusBar = False
Application.DisplayStatusBar = bln
End Sub

Sub PlayWAVFile(Filename As String, Optional Async As Boolean = True)
If Async Then
Call PlaySound(Filename, 0&, SND_ASYNC Or SND_FILENAME)
Else
Call PlaySound(Filename, 0&, SND_SYNC Or SND_FILENAME)
End If
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Full Monty]

Bob,
It works great!

And I am still laughing about your Full Monty comment!

Thanks for all your help!!!!!!!!!!!!!!!!!!!!!!!!!!