I want to display the time in a worksheet but want it to update automatically like a clock

It is to be used like a clock at a football game - any ideas

I have done it by using a macro to paste the time the game starts in a cell and then use Now()-start time to give elapsed time

I then have a macro to f9

I'm sure it can be done automaticly every second but don't know how

Any ideas?

Check out John Walkenbauch's utility:

http://j-walk.com/ss/excel/files/clockchart.htm

Regards,
Greg


-----Original Message-----
I want to display the time in a worksheet but want it to
update automatically like a clock.

It is to be used like a clock at a football game - any
ideas.

I have done it by using a macro to paste the time the
game starts in a cell and then use Now()-start time to
give elapsed time.

I then have a macro to f9

I'm sure it can be done automaticly every second but
don't know how.

Any ideas?
.

Thanks Greg - Nice piece of code

Now I just have to understand it so I can intergrate it into my project.

To start you off, try something like:

Option Explicit
Dim ClockRunning As Boolean

Sub StartClock()
ClockRunning = True
ControlClock
End Sub

Sub StopClock()
ClockRunning = False
End Sub

Sub ControlClock()
If ClockRunning = True Then
Range("A1") = Format(Now, "hh:mm:ss")
Application.OnTime Now + TimeValue("00:00:01"), "ControlClock"
End If
End Sub


To start the clock, just run the StartClock macro, and the StopClock
macro to stop the code.

You can expand the functionality from here to do all sorts of funky things

Hope this helps.

Scott

Easty wrote:

I want to display the time in a worksheet but want it to update automatically like a clock.

It is to be used like a clock at a football game - any ideas.

I have done it by using a macro to paste the time the game starts in a cell and then use Now()-start time to give elapsed time.

I then have a macro to f9

I'm sure it can be done automaticly every second but don't know how.

Any ideas?

..... and for the elapsed time, perhaps something like this?

Option Explicit
Dim ClockRunning As Boolean
Dim StartTime

Sub StartClock()
ClockRunning = True
StartTime = Now
ControlClock
End Sub

Sub StopClock()
ClockRunning = False
End Sub

Sub ControlClock()
Dim ElapsedHours, ElapsedMinutes, ElapsedSeconds

ElapsedHours = "0"
ElapsedMinutes = "00"

If ClockRunning = True Then
ElapsedSeconds = DateDiff("s", StartTime, Now())
If ElapsedSeconds = 3600 Then
ElapsedHours = Int(ElapsedSeconds / 3600)
ElapsedSeconds = ElapsedSeconds - ElapsedHours * 3600
End If
If ElapsedSeconds = 60 Then
ElapsedMinutes = Int(ElapsedSeconds / 60)
ElapsedSeconds = ElapsedSeconds - ElapsedMinutes * 60
If ElapsedMinutes < 10 Then ElapsedMinutes = "0" & ElapsedMinutes
End If
If ElapsedSeconds < 10 Then ElapsedSeconds = "0" & ElapsedSeconds

Range("A1") = ElapsedHours & ":" & ElapsedMinutes & ":" & ElapsedSeconds
Application.OnTime Now + TimeValue("00:00:01"), "ControlClock"
End If

End Sub


Easty wrote:

I want to display the time in a worksheet but want it to update automatically like a clock.

It is to be used like a clock at a football game - any ideas.

I have done it by using a macro to paste the time the game starts in a cell and then use Now()-start time to give elapsed time.

I then have a macro to f9

I'm sure it can be done automaticly every second but don't know how.

Any ideas?

Or perhaps a little shorter:

Dim ClockRunning As Boolean
Dim StartTime

Sub StartClock()
ClockRunning = True
StartTime = Now
Range("a1").NumberFormat = "[hh].mm.ss"
ControlClock
End Sub

Sub StopClock()
ClockRunning = False
End Sub

Sub ControlClock()
If ClockRunning = True Then
Range("a1").Value = Now - StartTime
Application.OnTime Now + TimeValue("00:00:01"), "ControlClock"
End If
End Sub



SmilingPolitely wrote:
.... and for the elapsed time, perhaps something like this?

Option Explicit
Dim ClockRunning As Boolean
Dim StartTime

Sub StartClock()
ClockRunning = True
StartTime = Now
ControlClock
End Sub

Sub StopClock()
ClockRunning = False
End Sub

Sub ControlClock()
Dim ElapsedHours, ElapsedMinutes, ElapsedSeconds

ElapsedHours = "0"
ElapsedMinutes = "00"

If ClockRunning = True Then
ElapsedSeconds = DateDiff("s", StartTime, Now())
If ElapsedSeconds = 3600 Then
ElapsedHours = Int(ElapsedSeconds / 3600)
ElapsedSeconds = ElapsedSeconds - ElapsedHours * 3600
End If
If ElapsedSeconds = 60 Then
ElapsedMinutes = Int(ElapsedSeconds / 60)
ElapsedSeconds = ElapsedSeconds - ElapsedMinutes * 60
If ElapsedMinutes < 10 Then ElapsedMinutes = "0" & ElapsedMinutes
End If
If ElapsedSeconds < 10 Then ElapsedSeconds = "0" & ElapsedSeconds

Range("A1") = ElapsedHours & ":" & ElapsedMinutes & ":" & ElapsedSeconds
Application.OnTime Now + TimeValue("00:00:01"), "ControlClock"
End If

End Sub


Easty wrote:

I want to display the time in a worksheet but want it to update
automatically like a clock.

It is to be used like a clock at a football game - any ideas.

I have done it by using a macro to paste the time the game starts in a
cell and then use Now()-start time to give elapsed time.

I then have a macro to f9
I'm sure it can be done automaticly every second but don't know how.

Any ideas?

Easty,

Here is a non-graphic approach which can be used as a countdown timer, a
clock, in a cell or the status bar.

http://tinyurl.com/2dua6

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Easty" wrote in message
...
I want to display the time in a worksheet but want it to update
automatically like a clock.

It is to be used like a clock at a football game - any ideas.

I have done it by using a macro to paste the time the game starts in a
cell and then use Now()-start time to give elapsed time.

I then have a macro to f9

I'm sure it can be done automaticly every second but don't know how.

Any ideas?