Lesson#80: Make an automatically refreshing bar clock in Excel
We will learn how to make an automatically refreshing bar clock in excel.
1. First I made three merged cells by putting these formulas
=HOUR(NOW())
=MINUTE(NOW())
=SECOND(NOW())
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
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
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.
if the sheet doesn’t start to refresh itself automatically after opening it.
excelabcd-bar clock to download the sheet.
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