Bob,

Solved my own problem - I had to use the code in a Standard Module, not a
Class Module - I was also able to use it with the original "Public"
declarations.

Sorry for the unnecessary radar blip.

Paul

"Paul Willman" wrote:

Bob,

I was trying to solve a similar problem as Tom and tried to use the code on
the Pearson Consulting site and ran into a couple of problems. The first I
solved by making the "Public" function and variable declarations "Private".
The second, which I can't figure out, is I am now getting a "Compile error:
Invalid use of AddressOf operator".

Any thoughts on how to resolve this?

Thanks,

Paul


"Bob Phillips" wrote:

I think you want OnTime. See http://www.cpearson.com/excel/ontime.htm

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Kragelund" wrote in message
...
Tom, you posted this brilliant piece of code (below) in a response to a
question with the title " Do while loop using time as the counter". I
found
it (potentially) extremely useful and intricate. So much so that I don't
know
how to adapt it to my own purpose, which is to initiate a validation
procedure on a sheet at user given intervals, for instance every two
minutes.
Can you (or somebody equally gifted) explain how I modify the code to loop
every e.g. 120 seconds until the user specifically orders the procedure to
stop?

Your help would be greatly appreciated!

Henrik



Public Declare Function SetTimer Lib "user32" ( _
ByVal hwnd As Long, ByVal nidevent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" ( _
ByVal hwnd As Long, ByVal nidevent As Long) As Long

Public timerid As Long
Public timerseconds As Single
Public Cnt As Long

Sub StartTimer()
timerseconds = 1 'how often to "pop"the timer
Cnt = 0
timerid = SetTimer(0&, 0&, timerseconds * 1000&, _
AddressOf Timerproc)
End Sub

Sub endtimer()
On Error Resume Next
KillTimer 0&, timerid
End Sub

Sub Timerproc(ByVal hwnd As Long, ByVal umsg As Long, _
ByVal nidevent As Long, ByVal dwtimer As Long)
Range("A1").Value = Cnt + 1
Beep
Cnt = Cnt + 1
If Cnt < 10 Then Exit Sub

endtimer
End Sub

write your log in the timerproc
--
Regards,
Tom Ogilvy