Hiya,

I need a cell in my worksheet (A4) to always show the current time (to the
minute). All I want to do is get excel to calculate every 60 seconds.

I've tried all sorts and using Pearson's stuff with application.ontime on
various options like workbook open, worksheet activate etc etc, but it don't
do sheet if you know what I mean.

I've also looked into 'timer' but I can't figure out how to make it work for
me.

It's is so so so so so so darned simple what I'm asking for... I'm
absolutely shocked I can't find it when I've searched everywhere on here for
it!

Can you help please?

Frustrated B

Hi,

Try this

Sub Clock()
ThisWorkbook.Sheets("Sheet1").Range("A4").Value = CDbl(Time)
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "Clock"
End Sub

Mike

"Basil" wrote:

Hiya,

I need a cell in my worksheet (A4) to always show the current time (to the
minute). All I want to do is get excel to calculate every 60 seconds.

I've tried all sorts and using Pearson's stuff with application.ontime on
various options like workbook open, worksheet activate etc etc, but it don't
do sheet if you know what I mean.

I've also looked into 'timer' but I can't figure out how to make it work for
me.

It's is so so so so so so darned simple what I'm asking for... I'm
absolutely shocked I can't find it when I've searched everywhere on here for
it!

Can you help please?

Frustrated B

Thanks for your speedy and good help.

I need to be able to stop it when I close the workbook rather than it try
and reopen the book immediately after. Can you help?

B

"Mike H" wrote:

Hi,

Try this

Sub Clock()
ThisWorkbook.Sheets("Sheet1").Range("A4").Value = CDbl(Time)
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "Clock"
End Sub

Mike

"Basil" wrote:

Hiya,

I need a cell in my worksheet (A4) to always show the current time (to the
minute). All I want to do is get excel to calculate every 60 seconds.

I've tried all sorts and using Pearson's stuff with application.ontime on
various options like workbook open, worksheet activate etc etc, but it don't
do sheet if you know what I mean.

I've also looked into 'timer' but I can't figure out how to make it work for
me.

It's is so so so so so so darned simple what I'm asking for... I'm
absolutely shocked I can't find it when I've searched everywhere on here for
it!

Can you help please?

Frustrated B

Have done it.

Thanks for tip. Here is full solution:

On workbook module:
Private Sub Workbook_Open()
StartClock
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopClock
End Sub

In module1:
Option Explicit
Public NextTick

Sub StartClock()
ThisWorkbook.Sheets("Sheet1").Range("A4").Value = Now
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "StartClock"
End Sub

Sub StopClock()
Application.OnTime NextTick, "StartClock", , False
End Sub

Thanks for help, do please tell if I can tidy / shove it all into the
workbook/worksheet modules (I hate having a 'modules' folder if I don't need
one!

Basil

"Basil" wrote:

Thanks for your speedy and good help.

I need to be able to stop it when I close the workbook rather than it try
and reopen the book immediately after. Can you help?

B

"Mike H" wrote:

Hi,

Try this

Sub Clock()
ThisWorkbook.Sheets("Sheet1").Range("A4").Value = CDbl(Time)
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "Clock"
End Sub

Mike

"Basil" wrote:

Hiya,

I need a cell in my worksheet (A4) to always show the current time (to the
minute). All I want to do is get excel to calculate every 60 seconds.

I've tried all sorts and using Pearson's stuff with application.ontime on
various options like workbook open, worksheet activate etc etc, but it don't
do sheet if you know what I mean.

I've also looked into 'timer' but I can't figure out how to make it work for
me.

It's is so so so so so so darned simple what I'm asking for... I'm
absolutely shocked I can't find it when I've searched everywhere on here for
it!

Can you help please?

Frustrated B

Why bother with the timer, why not save the workbook every 60 secs

Public nTime as Double

Sub Clock()
ThisWorkbook.Save
nTime = Now + TimeValue("00:01:00")
Application.OnTime nTime, "Clock"
End Sub


and in ThisWorkbook


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Ontime nTime,"Clock",,False
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Basil" wrote in message
...
Thanks for your speedy and good help.

I need to be able to stop it when I close the workbook rather than it try
and reopen the book immediately after. Can you help?

B

"Mike H" wrote:

Hi,

Try this

Sub Clock()
ThisWorkbook.Sheets("Sheet1").Range("A4").Value = CDbl(Time)
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "Clock"
End Sub

Mike

"Basil" wrote:

Hiya,

I need a cell in my worksheet (A4) to always show the current time (to
the
minute). All I want to do is get excel to calculate every 60 seconds.

I've tried all sorts and using Pearson's stuff with application.ontime
on
various options like workbook open, worksheet activate etc etc, but it
don't
do sheet if you know what I mean.

I've also looked into 'timer' but I can't figure out how to make it
work for
me.

It's is so so so so so so darned simple what I'm asking for... I'm
absolutely shocked I can't find it when I've searched everywhere on
here for
it!

Can you help please?

Frustrated B

Because it takes a lot longer to save the workbook.

I improved the code further. I entered in the NOW() function into cell A4,
and used this StartClock code instead:

Sub StartClock()
Calculate
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "StartClock"
End Sub

This is the only function on the sheet so it works just as quick, but the
key benefit is that when the other bits of code are running, the ticker will
still continue to tick (unlike the previous solution where it would pause).

B

"Bob Phillips" wrote:

Why bother with the timer, why not save the workbook every 60 secs

Public nTime as Double

Sub Clock()
ThisWorkbook.Save
nTime = Now + TimeValue("00:01:00")
Application.OnTime nTime, "Clock"
End Sub


and in ThisWorkbook


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Ontime nTime,"Clock",,False
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Basil" wrote in message
...
Thanks for your speedy and good help.

I need to be able to stop it when I close the workbook rather than it try
and reopen the book immediately after. Can you help?

B

"Mike H" wrote:

Hi,

Try this

Sub Clock()
ThisWorkbook.Sheets("Sheet1").Range("A4").Value = CDbl(Time)
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "Clock"
End Sub

Mike

"Basil" wrote:

Hiya,

I need a cell in my worksheet (A4) to always show the current time (to
the
minute). All I want to do is get excel to calculate every 60 seconds.

I've tried all sorts and using Pearson's stuff with application.ontime
on
various options like workbook open, worksheet activate etc etc, but it
don't
do sheet if you know what I mean.

I've also looked into 'timer' but I can't figure out how to make it
work for
me.

It's is so so so so so so darned simple what I'm asking for... I'm
absolutely shocked I can't find it when I've searched everywhere on
here for
it!

Can you help please?

Frustrated B