Clock ‘Cell’ in workbooks (2003 SP2)

Topic

New Reply

Good afternoon

Using the following code I can have a clock running all of the time in a cell on a workbook

Sub TimerMe()
Range(“B1”).Value = 1
Do While Range(“B1”).Value = 1
Application.OnTime Now + _
TimeValue(“00:00:01”), “Refresh”
Exit Do
Loop
End Sub
Sub Refresh()
Calculate
If Range(“B1”).Value = 1 Then
TimerMe
Else
Exit Sub
End If
End Sub

Sub stopClock()
Range(“B1”).Value = 0
End Sub

Q1: Can I have it run immediately without the stop and start buttons being present

Q2: What would I need to add to make it run in all worksheets in an open workbook

Q3: I am trying to show the time in London and the time in many other Countries (there are 255 tabs in the WB) so I have tried =A1+5 for example to increase the time by 5 hours but all that does is shows the date + 5 days and the current time in the target cell.

Any help appreciated

Cheers

Steve

Reply | Quote

Replies

Why would you want to do this in a workbook? It has serious disadvantages: it will interfere with editing the workbook and it will disable the undo feature.

There are many clock gadgets for Windows, some with the ability to display clocks for different time zones. Do a Google search or ask about it in our Other Software Applications forum.

Reply | Quote

Hi Hans

Thanks for the suggestion, the reason that I wanted to show the times is because I have been charged with writing a service guide for all of the destinations that we service worldwide, I thought that showing the GMT (UCT) time zone along with the time in that specific Country would enhance it for the users.

Cheers

Steve

Reply | Quote

[indent]

---

Q3: I am trying to show the time in London and the time in many other Countries (there are 255 tabs in the WB) so I have tried =A1+5 for example to increase the time by 5 hours but all that does is shows the date + 5 days and the current time in the target cell.

---

[/indent]

You can use =A1+5/24 to increment the clock by 5 hours

Reply | Quote

Hi Owen

Thanks for that, I did actually adopt something similar by putting =A1+Time(4,0,0) which increased the =now() time in A1 by 4 hours and =A1-Time(4,0,0) to deduct hours, I then adjusted each country time by using an online world clock and this works fine for me.

An explanation for anybody wanting to use this

The numbers inside the brackets represent hh,mm,ss so to increase by say 3.5 hours you would put +TIME(03.30.00) and to decrease by 3.5 hours -TIME(03.30.00) what is good about this is it adjusts the date for you as well so if at 16:00 today I opened the Australia page it would show my date and time correctly 26.6.2008 16:00 and Australias as 27.6.08 01:00

I just need to work out the winter time changes now

Cheers

Steve

Editted to remove wrong word

Reply | Quote

Hi

I know this was an old post, but I am trying to get this to work.

I am using Excell 2003. I have pasted the code provided in a module and add a now() function into A1, but I can not get the clock to run automatically. Can any body please show or explain to me what I am doing wrong.

I would appreciate it.

Thank you.

Reply | Quote

You need to ensure that B1 = 1, and then activate the “Refresh” macro.

However, I would advise that you take note of Hans words of caution higher up in this thread (post #2)

Reply | Quote

Hi

I did take note of it, I just want to understand how it works. I am not going to display it while I am working on a workbook. I did read many posts on other forums etc offering clocks and they work very nicely, but all I want to do here is having the clock run automatically inside a cell in the spreadsheet.

How do I activate the Refresh Macro? I thought all I have to do is changing B1=0 and then change it back to B1=1, but that does not work, the clock is not running, it only updates everytime you change B1.

Any other suggestion would be appreciated.

Reply | Quote

Hi

Thank you, I got it running now: Tools> Macros > run Refresh macro

Thank you for the help

Reply | Quote

Hi

After many hours of searching, testing etc I got hold of the script for a clock that does not seem to have any of the problems that some of the clocks have that display in an Excel cell.

Some of the problems with some clocks are that they stop running when you open a new workbook or change to another workbook. Some just crash Excel when you change workbooks.

The attached clock does not have any of these problems. If you have 2 workbooks open and use Windows > Arrange > tile Ok with one of the sheets with the attached clock in it you will notice that the clock stop running when you enter edit mode on the 2nd workbook but as soon as you press Enter it runs again. It also has not crach the workbook or Excel.

Then I must say that it contain some code that I have not seen in any of the other Excel clocks, maybe some of our wise men can enlighten us why this clock is different and if there are any other dangers or concerns in using the attached clock (I say this with the concerns from Hans mentioned earlier in mind)

Reply | Quote

That uses Windows timers rather than the Excel one, but it will still disable the Undo feature.

Reply | Quote