asked on

Refresh current Date & Time in Excel 2007

Hello,

I have got the =NOW() function entered in a cell to display the current Date and Time. Right now it only refreshes when I enter or change the data in a cell somewhere in the worksheet. Can you tell me how to refresh it every 1 min or 2 min automatically, regardless of entries being made?

I've tried going to the Data tab and clicking the dropdown arrow under the Refresh button but the Connection Properties option is grayed out so I can't go in to set automatic refreshing.

Any suggestions would be much appreciated.

Thanks

SOLUTION

rajvja

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

krishnakrkc

Hi,

try

Kris

Dim NextRefresh As Date
Sub StartRefresh()
RefreshNow
End Sub
Private Sub RefreshNow()
NextRefresh = Format(Now + TimeValue("00:00:05"), "dd-mm-yyyy hh:mm:ss")
With Range("a1")
    .NumberFormat = "dd-mm-yyyy hh:mm:ss"
    .Value = NextRefresh
End With
Application.OnTime NextRefresh, "StartRefresh"
End Sub
Sub StopRefresh()
On Error Resume Next
Application.OnTime NextRefresh, "StartRefresh", , 0
End Sub

Open in new window

krishnakrkc

replace

TimeValue("00:00:05")

with

TimeValue("00:02:00")

for two minute gap

Kris

Steve_Brady

ASKER

Thanks,

Can you tell me exactly what to do with above code? i.e. copy/paste? paste where?, do I open something or paste in a cell? etc. Sorry, I'm not familiar with programming.

Thanks

ASKER CERTIFIED SOLUTION

krishnakrkc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve_Brady

ASKER

Thnx Kris...that's just what I needed. I appreciate the detail. One thing: can u tell me what Ctrl+
F11 does (i.e. is there another way to do it?) cuz I've got F11 set to do another function.

Steve_Brady

ASKER

For your "Insert" step after Ctrl+F11, is that the Insert tab or something else? I'm in Excel 2007.

SOLUTION

krishnakrkc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Refresh current Date &amp; Time in Excel 2007

Refresh current Date & Time in Excel 2007