ExcelBanter - Timer Alarm

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Timer Alarm (https://www.excelbanter.com/excel-programming/326122-timer-alarm.html)

I'm stumped. Anyone know how I might trigger a sound alarm
when a cell reaches a certain time?

I have estimated times in one column. The next column I have actual
time. The third column I have column 2 minus column 1. I would
also like excel to trigger an alarm sound if the cell in column 3
is greater than "0". (in other words if the estimated times are
exceeded)

Is this possible?

Something like this

Private Sub Worksheet_Calculate()
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
For Each cell In Columns("C:C")
If cell.value 0 Then
MsgBox "done"
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"jimbo" wrote in message
ups.com...
I'm stumped. Anyone know how I might trigger a sound alarm
when a cell reaches a certain time?

I have estimated times in one column. The next column I have actual
time. The third column I have column 2 minus column 1. I would
also like excel to trigger an alarm sound if the cell in column 3
is greater than "0". (in other words if the estimated times are
exceeded)

Is this possible?

I guess a msgbox pops up with a sound, probably wanted
a wave file though. Times done in Excel are usually a
big waste of a processor.

For playing a .wav file see
' Tip 59 Playing Sound From Excel
' http://www.j-walk.com/ss/excel/tips/tip59.htm

Option Explicit
Declare Function sndPlaySound32 Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
'your own subroutine can be called from a worksheet event macro
Sub Double_beep()
Call sndPlaySound32("c:\i386\ringout.wav", 0)
End Sub

' Example of an Event Macro -- you will need to make playvalue100 in
' a regular module
' Option Explicit
' Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' If Target.Address(0, 0) = "A1" And Target.Value = 100 Then
' playvalue100
' End If
' End Sub

An external timer, or a timer in another application would probably
better, unless you are triggering the start of the timer on an Excel Event.

Timer Wizard - freeware alarm eggtimer software for windows, allows
you to easily and quickly set a reminder for an event in the future. You
can choose how you wish to be alerted.
http://www.siliconmachines.net/timerwiz/index.htm

might find this interesting if you want to play every sound onyou system someday.
http://www.mvps.org/dmcritchie/excel/code/beeps.txt

HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Bob Phillips" wrote ...
Something like this

Private Sub Worksheet_Calculate()
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
For Each cell In Columns("C:C")
If cell.value 0 Then
MsgBox "done"
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

"jimbo" wrote in message
ups.com...
I'm stumped. Anyone know how I might trigger a sound alarm
when a cell reaches a certain time?

I have estimated times in one column. The next column I have actual
time. The third column I have column 2 minus column 1. I would
also like excel to trigger an alarm sound if the cell in column 3
is greater than "0". (in other words if the estimated times are
exceeded)

Is this possible?

That was my laziness Dave, I just tried to show the principle, and leave the
rest to the OP.

Bob

"David McRitchie" wrote in message
...
I guess a msgbox pops up with a sound, probably wanted
a wave file though. Times done in Excel are usually a
big waste of a processor.

For playing a .wav file see
' Tip 59 Playing Sound From Excel
' http://www.j-walk.com/ss/excel/tips/tip59.htm

Option Explicit
Declare Function sndPlaySound32 Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
'your own subroutine can be called from a worksheet event macro
Sub Double_beep()
Call sndPlaySound32("c:\i386\ringout.wav", 0)
End Sub

' Example of an Event Macro -- you will need to make playvalue100 in
' a regular module
' Option Explicit
' Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' If Target.Address(0, 0) = "A1" And Target.Value = 100 Then
' playvalue100
' End If
' End Sub

An external timer, or a timer in another application would probably
better, unless you are triggering the start of the timer on an Excel
Event.

Timer Wizard - freeware alarm eggtimer software for windows, allows
you to easily and quickly set a reminder for an event in the future. You
can choose how you wish to be alerted.
http://www.siliconmachines.net/timerwiz/index.htm

might find this interesting if you want to play every sound onyou system
someday.
http://www.mvps.org/dmcritchie/excel/code/beeps.txt

HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Bob Phillips" wrote ...
Something like this

Private Sub Worksheet_Calculate()
Dim cell As Range

On Error GoTo ws_exit:
Application.EnableEvents = False
For Each cell In Columns("C:C")
If cell.value 0 Then
MsgBox "done"
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

"jimbo" wrote in message
ups.com...
I'm stumped. Anyone know how I might trigger a sound alarm
when a cell reaches a certain time?

I have estimated times in one column. The next column I have actual
time. The third column I have column 2 minus column 1. I would
also like excel to trigger an alarm sound if the cell in column 3
is greater than "0". (in other words if the estimated times are
exceeded)

Is this possible?

Hi Bob,
You showed an example of the Calculate Event to show
how to test, and that is more important than the wave file.
And I hadn't really noticed it was entire columns to be checked
not just one cell. So egg timer is out.

"Bob Phillips" wrote ...
That was my laziness Dave, I just tried to show the principle,
and leave the rest to the OP.

David McRitchie wrote:
Hi Bob,
You showed an example of the Calculate Event to show
how to test, and that is more important than the wave file.
And I hadn't really noticed it was entire columns to be checked
not just one cell. So egg timer is out.

"Bob Phillips" wrote ...
That was my laziness Dave, I just tried to show the principle,
and leave the rest to the OP.

Thanks so much guys. I've decided to change things around
a bit. I will just use one cell at a time. As it is, I use
autocomplete...
10m to become 10 minutes which currently displays as 12:10:00 AM.
20m would be 20 minutes displaying 12:20:00 AM and so on (I format
these as 10:00 and 20:00) I'm probably doing everything the hard way.
But what I want to do now is have the timer go off and beep in 10
minutes from the starting point where I input the 10m or 20m. I'm
probably a knothead for trying it this way, Any help appreciated