if I have the NOW() function say in cell A1
and then function =Text(A1, "hh:mm:ss") in cell B1, how do I get the time
display on the screen refreshed every second or so..

any help will be appreciated

John

John,

You don't, and nor you shouldn't as it will consume a lotr of system
resources.

However ... if you must, here is some code you can do it with It can run the
clock in a cell, or in the status bar. There is a lot of code here,
including some code to show how it would run from a
worksheet. If you want a workbok just mail me for it.

Firstly, create 3 control toolbar buttons on a worksheet called put this
code in a normal code module cmdStartClock, cmdStopClock, cmdRestartClock,
and 2 radio buttons called optCell and optStatus. Then put this code in that
sheet's code module

Option Explicit

Private Sub cmdStartClock_Click()
Range("C1").Value = Format(time, "hh:mm:ss")
StartClock
End Sub

Private Sub cmdRestartClock_Click()
RestartClock
End Sub

Private Sub cmdStopClock_Click()
StopClock
End Sub

Private Sub optCell_Click()
ClockView = "Cell"
End Sub

Private Sub optStatus_Click()
ClockView = "Status Bar"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo sub_exit:
If Not Intersect(Target, Range("hide")) Is Nothing Then
Application.DisplayFormulaBar = False
Else
Application.DisplayFormulaBar = True
End If

sub_exit:
Application.EnableEvents = True
End Sub


Then put this code in a normal code module.

Option Explicit

Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

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

Public ClockView As String

Private oldStatusBar
Private WindowsTimer As Long

Public Function cbkRoutine(ByVal Window_hWnd As Long, _
ByVal WindowsMessage As Long, _
ByVal EventID As Long, _
ByVal SystemTime As Long) As Long
Dim CurrentTime As String
On Error Resume Next
If ClockView = "Status Bar" Then
Application.StatusBar = Format(Now, "Long Time")
Else
Range("clock").Value = Format(Now, "Long Time")
End If
End Function

Sub StartClock()
If ClockView = "Status Bar" Then
oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.StatusBar = Format(Now, "Long Time")
Else
Range("clock").Value = Format(Now, "Long Time")
End If
fncWindowsTimer 1000, WindowsTimer '1 sec
End Sub

Sub StopClock()
fncStopWindowsTimer
If ClockView = "Status Bar" Then
Application.StatusBar = False
Application.DisplayStatusBar = oldStatusBar
End If
End Sub

Sub RestartClock()
If ClockView = "Status Bar" Then
oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
End If
fncWindowsTimer 1000, WindowsTimer '1 sec
End Sub

Public Function fncWindowsTimer(TimeInterval As Long, WindowsTimer As Long)
As Boolean
WindowsTimer = 0
'if Excel2000 or above use the built-in AddressOf operator to
'get a pointer to the callback function
If Val(Application.Version) 8 Then
WindowsTimer = SetTimer(hWnd:=FindWindow("XLMAIN",
Application.Caption), _
nIDEvent:=0, _
uElapse:=TimeInterval, _
lpTimerFunc:=AddrOf_Callback_Routine)
Else 'use K.Getz & M.Kaplan function to get a pointer
WindowsTimer = SetTimer(hWnd:=FindWindow("XLMAIN",
Application.Caption), _
nIDEvent:=0, _
uElapse:=TimeInterval, _
lpTimerFunc:=AddrOf("cbkRoutine"))
End If

fncWindowsTimer = CBool(WindowsTimer)

End Function

Public Function fncStopWindowsTimer()
KillTimer hWnd:=FindWindow("XLMAIN", Application.Caption), _
nIDEvent:=0 'WindowsTimer
End Function

Then this in another

Option Explicit

Private Declare Function GetCurrentVbaProject Lib "vba332.dll" _
Alias "EbGetExecutingProj" _
(hProject As Long) As Long

Private Declare Function GetFuncID Lib "vba332.dll" _
Alias "TipGetFunctionId" _
(ByVal hProject As Long, _
ByVal strFunctionName As String, _
ByRef strFunctionID As String) As Long

Private Declare Function GetAddr Lib "vba332.dll" _
Alias "TipGetLpfnOfFunctionId" _
(ByVal hProject As Long, _
ByVal strFunctionID As String, _
ByRef lpfnAddressOf As Long) As Long

'---------------------------------------------------------------------------
-
Public Function AddrOf(CallbackFunctionName As String) As Long
'---------------------------------------------------------------------------
-
'AddressOf operator emulator for Office97 VBA
'Authors: Ken Getz and Michael Kaplan
'---------------------------------------------------------------------------
-
Dim aResult As Long
Dim CurrentVBProject As Long
Dim strFunctionID As String
Dim AddressOfFunction As Long
Dim UnicodeFunctionName As String

'convert the name of the function to Unicode system
UnicodeFunctionName = StrConv(CallbackFunctionName, vbUnicode)

'if the current VBProjects exists...
If Not GetCurrentVbaProject(CurrentVBProject) = 0 Then
'...get the function ID of the callback function, based on its
'unicode-converted name, to ensure that it exists
aResult = GetFuncID(hProject:=CurrentVBProject, _
strFunctionName:=UnicodeFunctionName, _
strFunctionID:=strFunctionID)
'if the function exists indeed ...
If aResult = 0 Then
'...get a pointer to the callback function based on
'the strFunctionID argument of the GetFuncID function
aResult = GetAddr(hProject:=CurrentVBProject, _
strFunctionID:=strFunctionID, _
lpfnAddressOf:=AddressOfFunction)
'if we've got the pointer pass it to the result of the function
If aResult = 0 Then
AddrOf = AddressOfFunction
End If

End If

End If

End Function

'---------------------------------------------------------------------------
-
Public Function AddrOf_Callback_Routine() As Long
'---------------------------------------------------------------------------
-
'Office97 VBE does not recognise the AddressOf operator;
'however, it does not raise a compile-error ...
'---------------------------------------------------------------------------
-
AddrOf_Callback_Routine = vbaPass(AddressOf cbkRoutine)
End Function

'---------------------------------------------------------------------------
-
Private Function vbaPass(AddressOfFunction As Long) As Long
'---------------------------------------------------------------------------
-
vbaPass = AddressOfFunction
End Function



--

HTH

Bob Phillips

"John" wrote in message
u...
if I have the NOW() function say in cell A1
and then function =Text(A1, "hh:mm:ss") in cell B1, how do I get the time
display on the screen refreshed every second or so..

any help will be appreciated

John

Thank you bob

definitely there is a bit of code behind it
I have taken the easy way out of this
I have put the command "Calculate" at few places in my code so each time the
code checks for expected events it also does a calc. This occurs every few
seconds, the PC is dedicated for data collection anyway.

many thanks

John

"John" wrote in message
u...
if I have the NOW() function say in cell A1
and then function =Text(A1, "hh:mm:ss") in cell B1, how do I get the time
display on the screen refreshed every second or so..

any help will be appreciated

John

John,

Probably the right decision <vbg!

Do you want me to send you the workbook, just out of interest?

Bob


"John" wrote in message
...
Thank you bob

definitely there is a bit of code behind it
I have taken the easy way out of this
I have put the command "Calculate" at few places in my code so each time
the
code checks for expected events it also does a calc. This occurs every few
seconds, the PC is dedicated for data collection anyway.

many thanks

John

"John" wrote in message
u...
if I have the NOW() function say in cell A1
and then function =Text(A1, "hh:mm:ss") in cell B1, how do I get the
time
display on the screen refreshed every second or so..

any help will be appreciated

John

Bob,

This sounds like a possible solution to a problem I am
trying to solve. Would you be so kind as to send me this
workbook?

Thanks much,
Walt Boraczek
-----Original Message-----
John,

Probably the right decision <vbg!

Do you want me to send you the workbook, just out of
interest?

Bob


"John" wrote in message
u...
Thank you bob

definitely there is a bit of code behind it
I have taken the easy way out of this
I have put the command "Calculate" at few places in my
code so each time
the
code checks for expected events it also does a calc.
This occurs every few
seconds, the PC is dedicated for data collection anyway.

many thanks

John

"John" wrote in message
u...
if I have the NOW() function say in cell A1
and then function =Text(A1, "hh:mm:ss") in cell B1,
how do I get the
time
display on the screen refreshed every second or so..

any help will be appreciated

John






.