?
Solved

Excel Macro timevalue from Cell

Posted on 2003-02-27
5
Medium Priority
?
823 Views
Last Modified: 2012-08-13
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
Comment
Question by:braddybrad
[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
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 50

Expert Comment

by:Dave Brett
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

by:
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

by:braddybrad
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

by:Dave Brett
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

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question