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

 

[erik.van.geit]

Hi,

perhaps you would be interested in another technique
why?

using a cell is often not a good way:
- calculation
- sheet and workbookevents are called
but the main problem is:
- no UNDO available (I do not want to live with that problem)

there are different possibilities to avoid those problems, but to my sense the most simple is using a textbox
http://www.mrexcel.com/board2/viewtopic.php?t=247923

kind regards,
Erik

 

[lecxez]

Thank you for the quick reply.
Your code works perfectly, thank you very much for the help.

Robert

 

[erik.van.geit]

Thank you for the quick reply.
Your code works perfectly, thank you very much for the help.

Robert

are you sure this was a reply for this topic ?

 

[VoG]

Hi Erik.

Please see http://www.mrexcel.com/board2/viewtopic.php?t=257008

A duplicate thank you

 

[Steveo59]

I can't seem to get this to work I get the message "Ambiguous Name Detected :Tick Toc"

 

[erik.van.geit]

I can't seem to get this to work I get the message "Ambiguous Name Detected :Tick Toc"

you must have two subs with the same name in the same module

 

[Steveo59]

I can't seem to get this to work I get the message "Ambiguous Name Detected :Tick Toc"

you must have two subs with the same name in the same module[/quot]

yes thank you. is it possible to have the time only and no date?

 

[erik.van.geit]

there is no date displayed in the code I am refering to
http://www.mrexcel.com/board2/viewtopic.php?t=247923
Only the time will appear in the textbox

I'm not font of the other code which changes a cell.
They probably used "Now" somewhere, which you can replace by "Time", but the easiest would be to change the format of the cell
Again be warned: that code has a major drawback (see previous reply)!

best regards,
Erik

 

[Legacy 98055]

Here is some code that will not interfere with cells or undo by showing the ticker in the application's caption. The timer will also continue updating even while you are in edit mode. I suppose you could use the status bar as well?

Example File: Steveo59.255805.zip

In a standard module.
<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> SetTimer <font color="#0000A0">Lib</font> "user32" ( _
       <font color="#0000A0">ByVal</font> hwnd <font color="#0000A0">As</font> Long, _
       <font color="#0000A0">ByVal</font> nIDEvent <font color="#0000A0">As</font> Long, _
       <font color="#0000A0">ByVal</font> uElapse <font color="#0000A0">As</font> Long, _
       <font color="#0000A0">ByVal</font> lpTimerFunc <font color="#0000A0">As</font> Long) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">Declare</font> <font color="#0000A0">Function</font> KillTimer <font color="#0000A0">Lib</font> "user32" ( _
       <font color="#0000A0">ByVal</font> hwnd <font color="#0000A0">As</font> Long, _
       <font color="#0000A0">ByVal</font> nIDEvent <font color="#0000A0">As</font> Long) <font color="#0000A0">As</font> <font color="#0000A0">Long</font>

  <font color="#0000A0">Private</font> TimerID <font color="#0000A0">As</font> <font color="#0000A0">Long</font>

  <font color="#0000A0">Sub</font> Tick(ByVal dwParamOrCancel <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> hwnd <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> Milliseconds <font color="#0000A0">As</font> Long, <font color="#0000A0">ByVal</font> nEventId <font color="#0000A0">As</font> Long)
       <font color="#0000A0">On</font> <font color="#0000A0">Error</font> <font color="#0000A0">Resume</font> <font color="#0000A0">Next</font>
       Application.Caption = Now
       Application.StatusBar = Now
       Range("A1").Value = Now
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Sub</font> TimerAction(Optional StartTimer <font color="#0000A0">As</font> <font color="#0000A0">Boolean</font> = False, <font color="#0000A0">Optional</font> Interval <font color="#0000A0">As</font> <font color="#0000A0">Long</font> = 1000)
       <font color="#0000A0">If</font> StartTimer <font color="#0000A0">Then</font> TimerID = SetTimer(0, TimerID, Interval, <font color="#0000A0">AddressOf</font> Tick)
       <font color="#0000A0">If</font> <font color="#0000A0">Not</font> StartTimer <font color="#0000A0">Then</font> <font color="#0000A0">Call</font> KillTimer(0, TimerID)
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("1031200717255371").value=document.all("1031200717255371").value.replace(/<br \/>\s\s/g,"");document.all("1031200717255371").value=document.all("1031200717255371").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("1031200717255371").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="1031200717255371" wrap="virtual">
Private Declare Function SetTimer Lib "user32" ( _
ByVal hwnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long

Private Declare Function KillTimer Lib "user32" ( _
ByVal hwnd As Long, _
ByVal nIDEvent As Long) As Long

Private TimerID As Long

Sub Tick(ByVal dwParamOrCancel As Long, ByVal hwnd As Long, ByVal Milliseconds As Long, ByVal nEventId As Long)
On Error Resume Next
Application.Caption = Now
Application.StatusBar = Now
Range("A1").Value = Now
End Sub

Sub TimerAction(Optional StartTimer As Boolean = False, Optional Interval As Long = 1000)
If StartTimer Then TimerID = SetTimer(0, TimerID, Interval, AddressOf Tick)
If Not StartTimer Then Call KillTimer(0, TimerID)
End Sub</textarea>

In your workbook.
<table width="100%" border="1" bgcolor="White" style="filterrogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New>  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Workbook_Open()
       TimerAction <font color="#0000A0">True</font>
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

  <font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> Workbook_BeforeClose(Cancel <font color="#0000A0">As</font> Boolean)
       TimerAction
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>

</FONT></td></tr></table><button onclick='document.all("1031200717339913").value=document.all("1031200717339913").value.replace(/<br \/>\s\s/g,"");document.all("1031200717339913").value=document.all("1031200717339913").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("1031200717339913").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="1031200717339913" wrap="virtual">
Private Sub Workbook_Open()
TimerAction True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
TimerAction
End Sub
</textarea>

Edited out error. The attachment reflects these changes.

 

[Steveo59]

This is the code i am using and it works well but i would like to have the time only and no date.

Dim NextTick As Date

Sub TickTock()
Range("U2").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