Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 829
  • Last Modified:

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
0
braddybrad
Asked:
braddybrad
  • 3
  • 2
1 Solution
 
Dave BrettVice President - Business EvaluationCommented:
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
 
Dave BrettVice President - Business EvaluationCommented:

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
 
braddybradAuthor Commented:
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
 
Dave BrettVice President - Business EvaluationCommented:
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
 
braddybradAuthor Commented:
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!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now