application.ontime - referring to time in a worksheet range in a procedure

[Duane]

I have programmed a procedure that starts when a database sheet is
activated and checks every 15 seconds to see if a database entry has
been updated. If so, it runs an updatepivots procedure; otherwise,
the macro for updating is not run. The looping continues until the
sheet is deactivated.

If I 'hard wire' the procedure for a specific number of seconds, the
macro runs OK, no problem. There are times when others want to change
the seconds for the looping cycle. Some of the people using the
workbook don't know VBA.I would prefer to do this by referring to a
range on a worksheet which they can change.

The original procedure was written for 15 seconds:

Sub updatecheck()
Dim nextcheck

Application.OnTime Now, "updatepivots" ' starts the checking cycle
nextcheck = Now + TimeValue("00:00:15")
Application.OnTime nextcheck, "updatecheck" ' starts the looping
End Sub

How do I refer to a range named 'timer' on the worksheet named
"OPENING", in the second line above which has the number of seconds
inputed to change the seconds part of the TimeValue?

Thanks

Duane

[Paul Bedford]

Hi,

Try this:

nextcheck = Now + TimeValue("00:00:" & Range
("Timer").Value))

The timer must be less than 60 seconds or this may have
unpredictable results.

Hope this helps,

Paul

>.
>

[Chip Pearson]

> The timer must be less than 60 seconds or this may have
> unpredictable results.

Don't use TimeValue. Use TimeSerial instead.

nextcheck = Now + TimeSerial(0, 0, Range("Timer").Value)

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"Paul Bedford" <Paul_b...@yellgroup.com> wrote in message
news:714401c1baff$61d09150$19ef2ecf@tkmsftngxa01...

[merjet]

Duane,

Now + 15 / (CLng(24) * 60 * 60) is equivalent to Now +

TimeValue("00:00:15")

and the 15 could be replaced by a variable.

HTH,
Merjet