How to Display a Live Updating Clock on Every Excel Worksheet
Question details
The user wants to display a continuously updating live clock on every worksheet in a workbook.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking real-time from within an active workbook across multiple sheets without manual intervention.
- Observed behavior
- Standard time formulas like =NOW() do not refresh continuously; they only update when the worksheet is recalculated or modified.
Ensure you save your workbook as an Excel Macro-Enabled Workbook (.xlsm) to allow the continuous live clock script to run properly.
Create a Live Clock Using VBA Application.OnTime
Use a VBA macro to force the application to update a specific cell with the current time every second.
Because spreadsheet formulas are static until recalculated, VBA is required to create a loop that updates the time automatically.
The Application.OnTime method schedules a procedure to run at a specified time, allowing you to create a continuously ticking clock without freezing the rest of your workbook.
Press Alt + F11 to open the Visual Basic for Applications (VBA) Editor. Click 'Insert' from the top menu and select 'Module'.
In the new module, create a subroutine (e.g., Sub UpdateClock). Write code that loops through all worksheets, sets a designated cell (like A1) to the 'Now' function, and then schedules itself to run again in one second using Application.OnTime Now + TimeValue("00:00:01"), "UpdateClock".
In the Project Explorer pane on the left, double-click 'ThisWorkbook'. Select 'Workbook' from the left dropdown and 'Open' from the right. Call your 'UpdateClock' subroutine here so the clock starts ticking as soon as the file is opened.
To prevent the application from reopening automatically after closing, you must cancel the OnTime event. Add a 'Workbook_BeforeClose' event in 'ThisWorkbook' that sets the schedule parameter of Application.OnTime to False.
Return to your spreadsheet, select the target cells, right-click and choose 'Format Cells'. Apply a Time format (e.g., hh:mm:ss). Finally, either reopen the workbook or manually run the macro to start the live clock.
Use the =NOW() Formula with Manual Recalculation
A simpler alternative if macros are restricted, requiring a manual trigger to update the time.
Run VBA Macros Seamlessly in WPS Office
WPS Office Spreadsheets provides excellent support for VBA macros, allowing you to run automated tasks like a live updating clock with ease. It is a lightweight, highly compatible alternative for advanced data management.
- 1. Download and Install: Download WPS Office for free and install it on your computer, then open your spreadsheet document.
- 2. Access Developer Tools: Navigate to the 'Tools' tab on the top ribbon and click 'Developer' to access the Macro and VBA environment.
- 3. Open VBA Editor: Click the 'VB Editor' button (or press Alt + F11) and paste your live clock VBA code into a new module.
- 4. Save and Execute: Save the document as a Macro-Enabled Workbook (.xlsm) and run the script to see your live clock in action.
Frequently Asked Questions
Why does my live clock stop updating when I edit a cell?
The application suspends all macro execution and background calculations while you are actively editing a cell. The live clock will resume its schedule once you press Enter and exit cell edit mode.
Will running a live clock macro slow down my workbook?
Running a macro every second can cause a slight performance impact, especially in large, complex workbooks with many dynamic formulas, as it may trigger frequent screen updating and recalculations.
How do I stop the VBA clock macro from running manually?
To stop the macro manually, you need to create a dedicated 'StopClock' subroutine that cancels the Application.OnTime schedule by setting its final 'Schedule' parameter to False, and then run that subroutine from the Developer tab.
Can I make the clock display across all worksheets simultaneously?
Yes. In your VBA code, instead of targeting a specific sheet, you can write a 'For Each ws In ThisWorkbook.Worksheets' loop that updates a designated cell (e.g., ws.Range("A1").Value = Now) on every worksheet during each tick.
