Solved

# Excel Macro timevalue from Cell

Posted on 2003-02-27
Medium Priority
823 Views
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
0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 50

Expert Comment

ID: 8040214
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
0

LVL 50

Accepted Solution

Dave Brett earned 80 total points
ID: 8040247

To use Sheet1 Cell 1 as the input (in seconds) try...

Public RunWhen As Double
Public Const cRunWhat = "Time"

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

End Sub

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

Cheers

Dave
0

Author Comment

ID: 8040468
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
0

LVL 50

Expert Comment

ID: 8040564
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
0

Author Comment

ID: 8040867
It works perfect. Thanks again.
Explanations were clear and complete.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
###### Suggested Courses
Course of the Month12 days, left to enroll