+ Reply to Thread
Results 1 to 11 of 11

How to display system time and date continuously on cell while

  1. #1
    Registered User
    Join Date
    06-20-2010
    Location
    Mal
    MS-Off Ver
    Excel 2007
    Posts
    25

    How to display system time and date continuously on cell while

    Hi ,

    With the matter above, i would like to get advise for displaying the system time and date continuously on a cell while another macro is running .

    I have a macro that will rerun every 30 seconds with the purpose to update the data on sheet. Meanwhile, i tend to display the system time conterminously where the seconds will tick always.

    I tried to work out for it and have my code written on the sheet (not module) but the cell doesn't show the system time. Would like to remind that my another macro is running.
    Please Login or Register  to view this content.
    Is there any way to correct my code or do the system time display continuously?
    Thank you very much!
    Last edited by Leith Ross; 06-22-2010 at 10:42 PM. Reason: Added Code Tags

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,672

    Re: How to display system time and date continuously on cell while

    Why don't you just display the clock in Date & Time Properties?

    And please read the forum rules about CODE tags.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    06-20-2010
    Location
    Mal
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: How to display system time and date continuously on cell while

    hi, Thanks for reply and i will take notes on the code tag.

    May i noe what does Guru means by display clock in date and time properties?

    I have use the date and time properties to display the execution time of my current macro (not being showed here). And yet how can i display the system time continuously while my current macro is executing?

    says that cell "A1" display the time and date once my current macro is refreshed, cell "A2" display the system time with its changing seconds.

    My current macro will be executed once the workbook is opened and will auto refresh every 30 seconds.

    The "seconds" of the clock display will keep on updating no matter the regardless on my current macro activities.

    Kindly advise please it is possible?

  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to display system time and date continuously on cell while

    It is possible using windows timers, but it's not supported and I'm not sure I recommend it (if it goes wrong it will almost certainly crash your computer).
    With that in mind, here's a demo courtesy of the ingenious mind of Jaafar Tribak.
    Attached Files Attached Files
    Remember what the dormouse said
    Feed your head

  5. #5
    Registered User
    Join Date
    06-20-2010
    Location
    Mal
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: How to display system time and date continuously on cell while

    Thank you for your reply very much. It does work find with my code but i encountered problem when i tried to change the format of time as below

    Please Login or Register  to view this content.
    I tend to display the Date and Time (in 12Format), but it fails to update the system time continuously. May i noe what is the problem? Is it something to do with the argument in SetTimer function?

    Other than that, what is the meaning by crash with my system? Might occur error due to lin 32?

    Please advise. Thank you very much.

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to display system time and date continuously on cell while

    You need to have a formula in the cell, not a static value.

  7. #7
    Registered User
    Join Date
    06-20-2010
    Location
    Mal
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: How to display system time and date continuously on cell while

    Yes, I tried to set format as before

    Please Login or Register  to view this content.
    But it stil doesn't work out... Where is my mistake?

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to display system time and date continuously on cell while

    You have removed this line:
    Please Login or Register  to view this content.
    hence the TimerProc has no idea which cell to calculate.
    And when I say crash, I mean your system will freeze or reboot. Use at your own peril.

  9. #9
    Registered User
    Join Date
    06-20-2010
    Location
    Mal
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: How to display system time and date continuously on cell while

    i have removed all the unnecessary line and adapt it to my code. After editing, the code works finely. My own macro running well and system time able to run continuously but in 24 hour format and without date.

    But when i changed the format to 12 Hours and append date, the system time unable to work well.


    Thank you for your advise again.

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: How to display system time and date continuously on cell while

    Since I don't know what you are using, I can't tell you. Why not just alter the format of the cell though?

  11. #11
    Registered User
    Join Date
    06-20-2010
    Location
    Mal
    MS-Off Ver
    Excel 2007
    Posts
    25

    Re: How to display system time and date continuously on cell while

    Hi, I have tried to modify the text format as below

    Please Login or Register  to view this content.
    and it works fine.

    Noticed that to display the time format in 12 hour, have to define the ampm as AM/PM ... not AMPM (thought it is accepted in other cases). On the other hand, i tried to fix the cell formula directly tru excel sheet, yet the format declaration in macro will overwrite my setting.

    Please share your option if u know what is the reason for above. :D

    Here by just for sharing how to display the date and time (12 format) always on a cell. and many thanks to Guru reference.
    Last edited by Orange.CL; 06-25-2010 at 04:30 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1