Hello;

In a worksheet single cell I have the formula =NOW(). I need that to run &
show time constantly. However, sometimes it just stops at a certian time and
won't refresh until some other action happens on the worksheet. Any
suggestions on how I can have the time (h,m,s) show in a cell on constant
basis?

Debbie

Via code


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, "UpdateTicker", , False
End Sub

Private Sub Workbook_Open()
Call UpdateTicker
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

in a standard code module, add

Option Explicit

Public nTime As Long

Sub UpdateTicker()
Worksheets(1).Range("A1").Value = Format(Time, "hh:mm:ss")
nTime = Now + TimeSerial(0, 0, 1)
Application.OnTime nTime, "UpdateTicker"
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Debbie Sheperd" wrote in message
...
Hello;

In a worksheet single cell I have the formula =NOW(). I need that to run
&
show time constantly. However, sometimes it just stops at a certian time
and
won't refresh until some other action happens on the worksheet. Any
suggestions on how I can have the time (h,m,s) show in a cell on constant
basis?

Debbie

Hi Bob,

i have inserted this part of the code in "ThisWorkbook":

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, "UpdateTicker", , False
End Sub
Private Sub Workbook_Open()
Call UpdateTicker
End Sub

Than i inserted "Module1" with this code:

Option Explicit
Public nTime As Long
Sub UpdateTicker()
Range("A1").Value = Format(Time, "hh:mm:ss")
nTime = Now + TimeSerial(0, 0, 1)
Application.OnTime nTime, "UpdateTicker"
End Sub

and when i open the workbook the time is inserted into cell A1 but it is not
refreshed any more - there is still the time when the workbook was open. Any
idea why it is not refreshed? Or just exactly write where should be which
part of the code inserted. Many thanks.

Henrich

- - -

€žBob Phillips" napÃ*sal (napÃ*sala):

Via code


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, "UpdateTicker", , False
End Sub

Private Sub Workbook_Open()
Call UpdateTicker
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

in a standard code module, add

Option Explicit

Public nTime As Long

Sub UpdateTicker()
Worksheets(1).Range("A1").Value = Format(Time, "hh:mm:ss")
nTime = Now + TimeSerial(0, 0, 1)
Application.OnTime nTime, "UpdateTicker"
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Debbie Sheperd" wrote in message
...
Hello;

In a worksheet single cell I have the formula =NOW(). I need that to run
&
show time constantly. However, sometimes it just stops at a certian time
and
won't refresh until some other action happens on the worksheet. Any
suggestions on how I can have the time (h,m,s) show in a cell on constant
basis?

Debbie

Sorry guys, a mis-declared variable.

Replace the line

Public nTime As Long

with

Public nTime As Double

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Henrich" wrote in message
...
Hi Bob,

i have inserted this part of the code in "ThisWorkbook":

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, "UpdateTicker", , False
End Sub
Private Sub Workbook_Open()
Call UpdateTicker
End Sub

Than i inserted "Module1" with this code:

Option Explicit
Public nTime As Long
Sub UpdateTicker()
Range("A1").Value = Format(Time, "hh:mm:ss")
nTime = Now + TimeSerial(0, 0, 1)
Application.OnTime nTime, "UpdateTicker"
End Sub

and when i open the workbook the time is inserted into cell A1 but it is
not
refreshed any more - there is still the time when the workbook was open.
Any
idea why it is not refreshed? Or just exactly write where should be which
part of the code inserted. Many thanks.

Henrich

- - -

"Bob Phillips" napísal (napísala):

Via code


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, "UpdateTicker", , False
End Sub

Private Sub Workbook_Open()
Call UpdateTicker
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

in a standard code module, add

Option Explicit

Public nTime As Long

Sub UpdateTicker()
Worksheets(1).Range("A1").Value = Format(Time, "hh:mm:ss")
nTime = Now + TimeSerial(0, 0, 1)
Application.OnTime nTime, "UpdateTicker"
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Debbie Sheperd" wrote in
message
...
Hello;

In a worksheet single cell I have the formula =NOW(). I need that to
run
&
show time constantly. However, sometimes it just stops at a certian
time
and
won't refresh until some other action happens on the worksheet. Any
suggestions on how I can have the time (h,m,s) show in a cell on
constant
basis?

Debbie

Bob,

PERFEKT!!! - you did it. MANY THANKS.

Henrich


€žBob Phillips" napÃ*sal (napÃ*sala):

Sorry guys, a mis-declared variable.

Replace the line

Public nTime As Long

with

Public nTime As Double

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Henrich" wrote in message
...
Hi Bob,

i have inserted this part of the code in "ThisWorkbook":

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, "UpdateTicker", , False
End Sub
Private Sub Workbook_Open()
Call UpdateTicker
End Sub

Than i inserted "Module1" with this code:

Option Explicit
Public nTime As Long
Sub UpdateTicker()
Range("A1").Value = Format(Time, "hh:mm:ss")
nTime = Now + TimeSerial(0, 0, 1)
Application.OnTime nTime, "UpdateTicker"
End Sub

and when i open the workbook the time is inserted into cell A1 but it is
not
refreshed any more - there is still the time when the workbook was open.
Any
idea why it is not refreshed? Or just exactly write where should be which
part of the code inserted. Many thanks.

Henrich

- - -

"Bob Phillips" napÃ*sal (napÃ*sala):

Via code


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, "UpdateTicker", , False
End Sub

Private Sub Workbook_Open()
Call UpdateTicker
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

in a standard code module, add

Option Explicit

Public nTime As Long

Sub UpdateTicker()
Worksheets(1).Range("A1").Value = Format(Time, "hh:mm:ss")
nTime = Now + TimeSerial(0, 0, 1)
Application.OnTime nTime, "UpdateTicker"
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Debbie Sheperd" wrote in
message
...
Hello;

In a worksheet single cell I have the formula =NOW(). I need that to
run
&
show time constantly. However, sometimes it just stops at a certian
time
and
won't refresh until some other action happens on the worksheet. Any
suggestions on how I can have the time (h,m,s) show in a cell on
constant
basis?

Debbie

Bob;

Thanks vm. that will do the trick. Do you know if I can also run another
concurrent On.Time procedure? Or will the second one get messed up 'cause
calling "UpdateTicker" every second?

Do you know why just using =Now() in a cell dosen't work?

Debbie

"Bob Phillips" wrote:

Via code


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, "UpdateTicker", , False
End Sub

Private Sub Workbook_Open()
Call UpdateTicker
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

in a standard code module, add

Option Explicit

Public nTime As Long

Sub UpdateTicker()
Worksheets(1).Range("A1").Value = Format(Time, "hh:mm:ss")
nTime = Now + TimeSerial(0, 0, 1)
Application.OnTime nTime, "UpdateTicker"
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Debbie Sheperd" wrote in message
...
Hello;

In a worksheet single cell I have the formula =NOW(). I need that to run
&
show time constantly. However, sometimes it just stops at a certian time
and
won't refresh until some other action happens on the worksheet. Any
suggestions on how I can have the time (h,m,s) show in a cell on constant
basis?

Debbie

Should be okay to run another. Only problem comes if they both have the same
scheduled time, don't know which executes first. Do make sure that you use a
different time variable, and you call the correct procedures <g

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Debbie Sheperd" wrote in message
...
Bob;

Thanks vm. that will do the trick. Do you know if I can also run another
concurrent On.Time procedure? Or will the second one get messed up 'cause
calling "UpdateTicker" every second?

Do you know why just using =Now() in a cell dosen't work?

Debbie

"Bob Phillips" wrote:

Via code


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nTime, "UpdateTicker", , False
End Sub

Private Sub Workbook_Open()
Call UpdateTicker
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

in a standard code module, add

Option Explicit

Public nTime As Long

Sub UpdateTicker()
Worksheets(1).Range("A1").Value = Format(Time, "hh:mm:ss")
nTime = Now + TimeSerial(0, 0, 1)
Application.OnTime nTime, "UpdateTicker"
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Debbie Sheperd" wrote in
message
...
Hello;

In a worksheet single cell I have the formula =NOW(). I need that to
run
&
show time constantly. However, sometimes it just stops at a certian
time
and
won't refresh until some other action happens on the worksheet. Any
suggestions on how I can have the time (h,m,s) show in a cell on
constant
basis?

Debbie