Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Clock 'Cell' in workbooks (2003 SP2)

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clock 'Cell' in workbooks (2003 SP2)

    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.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clock 'Cell' in workbooks (2003 SP2)

    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
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Clock 'Cell' in workbooks (2003 SP2)

    <hr>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.
    <hr>

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

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clock 'Cell' in workbooks (2003 SP2)

    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 <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Cheers

    Steve

    Editted to remove wrong word
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Attached Files Attached Files
    Regards
    Kobus

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)

  8. #8
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Regards
    Kobus

  9. #9
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

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

    Thank you for the help
    Regards
    Kobus

  10. #10
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)
    Attached Files Attached Files
    Regards
    Kobus

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    That uses Windows timers rather than the Excel one, but it will still disable the Undo feature.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •