Timer Alarm

[jimbo]

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?

[Bob Phillips]

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" <jamesfonda1@yahoo.com> wrote in message
news:1111692977.535680.41560@l41g2000cwc.googlegroups.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?
>

[David McRitchie]

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" <bob.phillips@notheretiscali.co.uk> 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" <jamesfonda1@yahoo.com> wrote in message
> news:1111692977.535680.41560@l41g2000cwc.googlegroups.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?
> >
>
>

[Bob Phillips]

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

Bob

"David McRitchie" <dmcritchie@msn.com> wrote in message
news:eyKFXBLMFHA.1472@TK2MSFTNGP14.phx.gbl...
> 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" <bob.phillips@notheretiscali.co.uk> 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" <jamesfonda1@yahoo.com> wrote in message
> > news:1111692977.535680.41560@l41g2000cwc.googlegroups.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?
> > >
> >
> >
>
>

[David McRitchie]

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" <bob.phillips@notheretiscali.co.uk> wrote ...
> That was my laziness Dave, I just tried to show the principle,
> and leave the rest to the OP.

[jimbo]

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" <bob.phillips@notheretiscali.co.uk> 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

[jimbo]

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" <bob.phillips@notheretiscali.co.uk> 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