Active Clock

[richblake74]

Is there anyway of getting an "Active Clock" in a cell.
I know the =now() thing, but this is not what I am looking for,
I would like to get a clock that updates by the minute, without having to select a cell, or make excel do a calculation.
Hope you are all having fun with excel 2007
richblake74

 

[VHS01]

richblake74,

<font face=Courier New>Put the following code in a code module. (Time format - 13:30)

<SPAN style="color:#00007F">Sub</SPAN> StartTime()
<SPAN style="color:#00007F">Dim</SPAN> TargetHour
    <SPAN style="color:#00007F">If</SPAN> Minute(Now) = 59 <SPAN style="color:#00007F">Then</SPAN>
        TargetHour = Hour(Now) + 1
    <SPAN style="color:#00007F">Else</SPAN>
        TargetHour = Hour(Now)
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    RunWhen = TimeSerial(TargetHour, Minute(Now) + 1, 0)
    RunWhat = "UpdateClock"
    Application.OnTime earliesttime:=RunWhen, Procedure:=RunWhat, schedule:=<SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> UpdateClock()
    Sheets("Sheet1").Range("A1").Value = "=Now()"
    StartTime
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

Put this code in "ThisWorkbook"

You will have to run the "StartTime" macro to get things going by putting it in <SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open() at startup.
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
    StartTime
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">To</SPAN> stop the timer when you close the book
<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_Close()
    Application.OnTime earliesttime:=RunWhen, Procedure:=cRunWhat, schedule:=<SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>


Hope this helps.
Gary
</FONT>

 

[richblake74]

Thank you

Does not seem to work but thanks for your help,,,
I am using the Mac version, maybe this is the problem..

 

[VHS01]

richblake74,

Well...lets make sure everything is in place.

Is your sheet named "Sheet1". If not change that in the code to be your sheet name.

The first code goes into code "module"
The second in the "ThisWorkbook"

To start the process, it is easiest to close and reopen the workbook.
You can also click anywhere in the StartTimer code and click the run (right) arrow above.

See if that helps.

Gary

 

[richblake74]

Gary

Thanks for all your efforst Gary..
Sub UpdateClock() this keeps getting highlighted, in yellow
I am not good at all with this VB stuff.
Tried many times.
I appreciate all the effort you have made.
I think I will just look at my Wrist Watch
Sincerely

 

[VoG]

This is a bit simpler:

Dim NextTick As Date

Sub TickTock()
Range("A1").Value = Now()
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "TickTock"
End Sub

Sub StopClock()
Application.OnTime earliesttime:=NextTick, procedure:="TickTock", schedule:=False
End Sub

ALT + F11 to open the Visual Basic Editor. Insert > Module. Copy the above code and paste it in. Then close the VBE.

ALT + F8, click on TickTock and click the Run button to start the clock. The time should be displayed in A1 - you can adjust the time format in A1 to suit.

To stop the clock ALT+F8, click StopClock and click Run.

 

[richblake74]

active Clock

that worked great, now final question...
do i have to Stop the clock to close the Excel program, i am guessing I do as each time I try and close the workbook, without stopping the clock, it keeps reopening.
Is there any way I can close the work book without having to stop the clock??????
One more thing also, If the clock is running and I try to go to another sheet in the book, I get a runtine error...

 

[VoG]

Re: active Clock

Is there any way I can close the work book without having to stop the clock??????

Yes

Right click the Excel logo just to the left of File on the menu and select View Code. Paste in the following

Private Sub Workbook_BeforeClose(cancel As Boolean)
Call StopClock
End Sub

then close the code window. When you close the workbook the timer will stop.

 

[richblake74]

Thank you sir,,,
now when I open it the clock should be running without me doing anything....
have a great week and thank you for all your help

 

[VoG]

Ah - to have the clock start automatically you need to add this to the code that you just placed in the workbook code module:

Private Sub Workbook_Open()
Call TickTock
End Sub