I need to have a actual time in a cell

October 21, 2010 at 04:40:03
Specs: Windows XP, 512
I need to have a actual time in a cell (Excell)& by using it need a sound alerm. In my worksheet there are some jobs to do during 12 hour period & I need reminder alerm when execute time comes. I got following code from here but its not working. There is a compilation error in line 3 & 5. please help.

Public RunWhen As Double
' Set interval to 1 second
Public Const cRunIntervalSeconds = 1
' Define name of procedure to run
Public Const cRunWhat = "The_Timer"

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime _
EarliestTime:=RunWhen, _
Procedure:=cRunWhat, _
Schedule:=True
End Sub

Sub The_Timer()
With [A1]
.Formula = "=now()"
.NumberFormat = "hh:mm:ss;@"
End With
StartTimer
End Sub


See More: I need to have a actual time in a cell

Report •


#1
October 21, 2010 at 05:20:18
A posting tip:

re: "There is a compilation error in line 3 & 5"

When posting in a Help forum such as this, you should post the text of the error.

There are so many different types of "compilation errors" that any suggestions we offer would be nothing more than guesses.

DerbyDad03
Office Forum Moderator


Report •

#2
October 22, 2010 at 08:49:02
Dear DerbyDad03

Compilation error problem solved when paste the script on module but still I am unable to solve my sound alerm problem.As discribed in my early post I want soung alert from worksheet when schedule time comes during 12 hour period. Please help


Report •

#3
October 22, 2010 at 10:47:43
Try this full set of code.

It includes much of what you already had (with some modifications) plus some "Sound Playing" code that I found on the web (with some modifications)

You'll need to put the full path name to a wav file in the TestPlayWavFile routine.

You'll also need to put the time that you want the wav file to play in B1.

Each time the code places the new time in A1, it will compare it to the time in B1 and run the TestPlayWaveFile routine when they match.

Public Declare Function sndPlaySound Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
Public RunWhen As Double
' Set interval to 1 second
Public Const cRunIntervalSeconds = 1
' Define name of procedure to run
Public Const cRunWhat = "The_Timer"


Sub PlayWavFile(WavFileName As String, Wait As Boolean)
    If Dir(WavFileName) = "" Then Exit Sub ' no file to play
    If Wait Then ' play sound before running any more code
        sndPlaySound WavFileName, 0
    Else ' play sound while code is running
        sndPlaySound WavFileName, 1
    End If
End Sub


Sub TestPlayWavFile()
    PlayWavFile _
       "C:\SomeWavFileName.wav", False
    MsgBox "Time to Do Whatever You Are Supposed To Do"
End Sub


Sub StartTimer()
  RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime _
    EarliestTime:=RunWhen, _
    Procedure:=cRunWhat, _
    Schedule:=True

End Sub

Sub The_Timer()
 With [a1]
  .Formula = "=NOW()"
  .NumberFormat = "hh:mm:ss;@"
    If TimeValue(Hour([a1]) & ":" & _
                 Minute([a1]) & ":" & _
                 Second([a1])) = _
       TimeValue(Hour([B1]) & ":" & _
                 Minute([B1]) & ":" & _
                 Second([B1])) Then TestPlayWavFile
 End With
  StartTimer
End Sub






Report •

Related Solutions

#4
October 22, 2010 at 17:30:18
Dear DerbyDad3

Thanks for your effort.
I need to be called to my computer at differing
times during the day(15 - 20). I found this code from web but it gives following error.

Run-Time error '1004'

Method 'On Time' of object '_Application' failed

When debug this following line highlited. Please check it & help me. I am new to coding. Thanks


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

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

Sub PlayWAV()
WAVFile = "C:\WINDOWS\Media\Alerm.wav"
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
End Sub

Sub SetAlarms()
Application.Calculate
Dim myCell As Range
For Each myCell In Range("A1", Range("A65536").End(xlUp))
Application.OnTime myCell.Value, "PlayWAV"
Next myCell
End Sub

Sub CancelAlarms()
Dim myCell As Range
For Each myCell In Range("A1", Range("A65536").End(xlUp))

Next myCell
End Sub



Report •

#5
October 22, 2010 at 20:04:56
I can't tell from your post which line is highlighted.

However, I can tell you this:

I pasted the code in a standard module and it ran just fine, sounding an alarm for each time I had listed in Column A.

P.S. I was also able to modify my code to sound an alarm for multiple times, but it's not as elegant as the code you found.


Report •

#6
October 23, 2010 at 22:41:28
Dear DerbyDad03

This is the line which highliting during run. It's not working in my standerd module. Is there anything incorrect in this line..
Application.OnTime myCell.Value, "PlayWAV"


Report •

#7
October 24, 2010 at 05:49:46
OnTime() was added to Application in Office 2K3. If you have Office 2K, you cannot use that function.

How To Ask Questions The Smart Way


Report •

#8
October 24, 2010 at 06:20:38
Did you read everything that I wrote in Response #5?

If the code worked fine in my workbook, then obviously there isn't anything wrong with the line you posted.

I would close all workbooks and start from scratch.

If any version of the code that is using the OnTime() is running in the background, you might run into strange problems.

In addition, you should be aware of this "feature" of OnTime:

If you do not cancel the OnTime method when you close the workbook, it will open the workbook at the next scheduled time and run whatever macro it is supposed to run. It's very possible that you still have an OnTime event scheduled and that can cause strange issues.


Report •

#9
October 28, 2010 at 23:33:50
Please tell me how can I cancel Ontime method.

Report •

#10
October 29, 2010 at 07:19:21
Cancelling the OnTime event depends on how you are using the OnTime method.

You seem to be able to find code on the web so Google the following string to see various methods and choose the one that works for you:

cancel Excel VBA ontime


Report •

Ask Question