Alarm timer in Excel

[Guest]

Hi, I have a spreadsheet written to calculate time intervals during a judging
night. There are several sessions of judging each of which has several items
to be judged. The idea is that each session of judging be given a time
interval based on the number of items to judge. The objective is to have the
judge finish each of the judging sessions by a fixed time (real time that
is). I have the spreadsheet working so that you enter the start time, the
finish time, the number of items in each session and the number of sessions
to be judged. All works OK.

What I would like to have happen is to ring an alarm (make a sound if
possible) to say that the judging time for a particular event has elapsed. In
other words, we know the start time, we know the time allowed for the judging
for that session, what we want is an alarm to ring after start + judging time.

I'm not very experienced with VBasic and hope to be able to (say) run a
macro attached to a button and simply dump the code into there.

Hope someone can help

Regards, Bob Cook

 

[Bob Phillips]

OnTime should work for you

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

Dim nTime As Double
Const nElapsed As Double = 120 ' 2 minutes

Public Sub StartTimer()
nTime = Now + nElapsed
Application.OnTime nTime, "Alarm"
End Sub

Public Sub Alarm()
palywavfile "some file path and name"
End Sub

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



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)