Lesson#80: Make an automatically refreshing bar clock in Excel

Lesson#80: Make an automatically refreshing bar clock in Excel

We will learn how to make an automatically refreshing bar clock in excel.

Make a auto refreshing bar clock in Excel
1. First I made three merged cells by putting these formulas
=HOUR(NOW())
=MINUTE(NOW())
=SECOND(NOW())
 

Make a auto refreshing bar clock in Excel

2. Select those cells one by one and click on Conditional Formatting>Data Bars>Solid Fills
I did put the min value 0 and max value 23 for hours and the min value 0 and max value 59 for minutes and second by selecting type number.

3. From the Developer Tab I have inserted a Button (Form Control) and clicked on New in the Assign Macro window

Make a auto refreshing bar clock in Excel

4. Opening the VBA tab I added this code

 
Sub Button2_Click()
Call refreshcell
End Sub
Sub refreshcell()
Range(“a1”).Calculate
Application.OnTime Now + TimeValue(“00:00:1”), “refreshcell”
End Sub

5. Edited the button text and the size just looks good.

 
6. Save this sheet as a macro-enabled workbook in .xlsm format. Now see every second this sheet is auto-refreshing the clock. I put that button to call the function refreshcell()
if the sheet doesn’t start to refresh itself automatically after opening it.

 

 
 
excelabcd-bar clock to download the sheet.
See the Tutorial Video
See also  Lesson#118: Format only cells that contain Conditional Formatting

Hi! I am Puspendu. I am the founder and author of Excelabcd. I am little creative person, blogger and Excel-maniac guy. I hope you enjoy my blog.

Leave a Reply

Your email address will not be published. Required fields are marked *

*