asked on

Excel Macro timevalue from Cell

Hi,

Here's my code to write the actual time in a new cell each 5 minutes, I have two open questions about it:
Sub Time()
Application.OnTime Now + TimeValue("00:05:00"), _
"Time"
[A65536].End(xlUp)(2) = Now
End Sub

Now, I'd like to be able to change the time by changing the value in a cell and avoid changing the code each time I want a new update time.
Second question: Can I have a button which could stop the macro when clicked? How?
Can anyone help?

Thanks

Dave

Chip Pearson recommends using variables for managing these routines. Attach the macro killtime() to a button to stop the Time() macro.

As far as an time input, you could pop up a userform asking for a value when the workbook opens, or use a cell value, range name etc. What way do you want to go?

Cheers

Dave

Public RunWhen As Double
Public Const cRunIntervalSeconds = 300 ' 5 minutes
Public Const cRunWhat = "Time"

Sub Time()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
[A65536].End(xlUp)(2) = Now

End Sub

Sub killtime()
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=False
End Sub

ASKER CERTIFIED SOLUTION

Dave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

braddybrad

ASKER

Thanks!
I tried both solutions, but:
first it said that cRunWhat and cannot be public, so I declared them private.
then it writes the first value but after the few seconds written in Cell 1,1 or in cRunIntervalSeconds it says "Microsoft Excel cannot find Macro ''U:/file.xls'!time'
.......

I prefer the second solution by using Sheet1 Cell 1 as the input.
thanks for helping

Dave

It sounds like you didnt put the code in a new module? If you put it in the 'Sheet' modules or 'ThisWorkbook' module the Const declaration won't work

In the VBE
Insert- New
Copy and paste the code into Module 1

Cheers

Dave

braddybrad

ASKER

It works perfect. Thanks again.
Explanations were clear and complete.
Brad