Updated Minutes Not Displaying On My Digital Clock

[Ark68]

I am refining how my digital clock displays on my worksheet. Rather than one formatted time display ("h:mm AM/PM"), I am approaching it now with one cell hosting the hours (N2), another the minutes(Q2), and a third the AM/PM (S2). I have a blinking ":" in P2 synced with the seconds (from this earlier inquiry) .

Public Sub UpdateTimer()

    With wsfront
        .Cells(2, 6).Value = Format$(Now, "mm/dd/yyyy") '1
        .Cells(2, 2).Value = Format$(Now, "hh:mm:ss")       '2
    
        .Cells(2, 14).Value = Format$(Now, "hh:mm:ss")
        .Cells(2, 17).Value = Format$(Now, "hh:mm:ss")
        .Cells(2, 19).Value = Format$(Now, "AM/PM")
        .Cells(6, 19).Value = Format$(Now, "ss")
    
    End With
    
    Application.OnTime Now + TimeSerial(0, 0, 1), "UpdateTimer"

End Sub

On My worksheet,
F2 is formatted as "mmmm dd, yyyy" and displays the current date properly. (October 9, 2020)
B2 is formatted as "h:mm AM/PM" and displays and updates the current time accurately. (3:19 PM)

N2 is formatted as "h" and appears to be displaying the current hour accurately (3)
Q2 is formatted as "mm" (to display the current minutes), but is not displayed accurately. It is displaying 01, not 19.
S2 is formatted as "AM/PM" and is displaying the appropriate value (PM)
S6 is formatted as "ss" and is displaying the incrementing seconds. (02)

When I click on cell N2, the current time displays in the formula bar. However, when I click on Q2, the formula bar displays "1900-01-00 3:19:02 PM"
What I believe to be happening, is my cell formatting of Q2 as "mm" is displaying the value of the month (01), rather than the minutes (19). (although the month is wrong, I would think it should be 10 ... I'm not sure where the date of 1900-01-00 is coming from when NOW is being used. It should be 2020-).

What must I do to overcome this?

 

[Jeffrey Mahoney]

First, I'm not sure why you're using vba to put the date and time in a cell when you can just use NOW() in the cell and format it correctly. VBA could recalculate just those cells if needed.

Q2 formatted as mm is looked at like the month number. To display minutes then add =minute(now()) and format the cell as a number without decimals

 

[Logit]

.
Try this version :

Option Explicit

Public RunWhen As Double
Public Const cRunIntervalSeconds = 1 ' 1 seconds
Public Const cRunWhat = "The_Sub"  ' the name of the procedure to run


Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
End Sub
Sub The_Sub()
Application.Cursor = xlNorthwestArrow                   'prevents mouse cusor blinking

With ActiveSheet
        .Cells(2, 6).Value = Format$(Now, "mm/dd/yyyy") '1
        .Cells(2, 2).Value = Format$(Now, "hh:mm:ss")       '2
    
        .Cells(2, 14).Value = Format$(Now, "hh:mm:ss")
        .Cells(2, 17).Value = Format$(Now, "hh:mm:ss")
        .Cells(2, 19).Value = Format$(Now, "AM/PM")
        .Cells(6, 19).Value = Format$(Now, "ss")
    
    End With
    
   ' Call StartTimer to schedule the procedure again
   StartTimer
End Sub

 

[Ark68]

Thanks folks for your replies. My apologies for the delay in acknowledging your suggestions.

First, I'm not sure why you're using vba to put the date and time in a cell when you can just use NOW() in the cell and format it correctly. VBA could recalculate just those cells if needed.

Thanks Jeffrey. Yes, that would be an option if I was just looking for a static value. But in this instance, I am looking to provide an actual clock type display, one that is updated by the second. Perhaps vba to update each second would be an alternative, but not sure how that would look in code.

Try this version :

Thank you Logit. I'm replaced my code with your suggestion. It still didn't overcome the improper minute display of cell Q2. Jeffry is correct ... it is not displaying the minutes. Although Jeffry, it's not displaying the month either. If it was, it would be displaying "10", but it displays "1". I am still curious why when I click on cell Q2, the formula bar reveals a cell value of 1900-01-00 and the current time incrementing by the second. Q2 (mind you a merged cell) is indeed formatted (incorrectly) as 'mm'. I have StartTimer in my workbook open event.

Here is my full "timer" code. It's placed in a standard module. Logit, you also suggested code on how to provide a blinking character in sync with seconds, which I have integrated (I will update that thread if we can get it working here). Perhaps you can look at it as I am unable to get the intended results. I am using a ":" at P2 as the character to blink.

Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
End Sub

Sub The_Sub()
    Application.Cursor = xlNorthwestArrow                   'prevents mouse cusor blinking

        With ActiveSheet
            .Cells(2, 6).Value = Format$(Now, "mm/dd/yyyy") '1
            .Cells(2, 2).Value = Format$(Now, "hh:mm:ss")   '2
        
            .Cells(2, 14).Value = Format$(Now, "hh:mm:ss")  'hours value
                                                            'P2 ":" startblink
            .Cells(2, 17).Value = Format$(Now, "hh:mm:ss")  'minutes value
            .Cells(2, 19).Value = Format$(Now, "AM/PM")     'AM / PM
            .Cells(6, 19).Value = Format$(Now, "ss")        'seconds
        
        End With
        
       ' Call StartTimer to schedule the procedure again
       StartTimer
End Sub

Sub StartBlink() 'blinking ':' at P2 in sync with seconds
  Dim xCell As Range
  Dim xTime As Variant
 
  Set xCell = Range("P2")
 
  With wsfront.Range("P2").Font
    If xCell.Font.Color = vbRed Then
        xCell.Font.Color = vbWhite
    Else
        xCell.Font.Color = vbRed
    End If
  End With
 
  xTime = Now + TimeSerial(0, 0, 1)
  Application.OnTime xTime, "'" & wsfront & "'! StartBlink", , True
 
End Sub

Thank you all for your time.

 

[Ark68]

Further to the StartBlink issue ...

I have StartBlink in my workbook open event, and am using a ":" at P2 as the character to blink. I am getting an error:
Cannot run the macro "D:\Logic\[TimeClock.xlsm]Launch'! StartBlink'. The macro may not be available in this workbook or all macros may be disabled.

This was after correcting the line

Application.OnTime xTime, "'" & wsfront & "'! StartBlink", , True

to

Application.OnTime xTime, "'" & wsfront.name & "'! StartBlink", , True

 

[Logit]

.
I've rewritten some of the code. You will need to download the workbook and view the macro code.

There is code in the ThisWorkbook Module that automatically starts the asterisk blinking. A button remain on the sheet to start the clock timer.
If you want the clock timer / display to start automatically add a call to the START TIMER macro in the ThisWorkbook module in similar manner
as the STARTBLINK macro.

Clock Blink Ttest.xlsm

 

[Ark68]

Logit, thank you! I have the blinking working now in my code. My error was with the Application.OnTime line where I referenced a worksheet instead of the workbook.

But I am still being challenged with the actual display now. I have removed the cell formatting of N2 (hours display) and Q2 (minutes display), so both cells are showing the same values formatted as hh:mm:ss.
I can get the hours to display by applying a "h" format to cell N2, but I still don't know how to return the minutes portion for cell Q2. I can't format the cell as "mm" as we know that displays the month value. How to format Q2 to display the minutes value of an everupdating 'now'?

 

[Logit]

.
In the macro Sub The_Sub ... use this macro instead :

Sub The_Sub()
Application.Cursor = xlNorthwestArrow                   'prevents mouse cusor blinking

With ActiveSheet
        .Cells(2, 6).Value = Format$(Now, "mm/dd/yyyy") '1
        .Cells(2, 2).Value = Format$(Now, "hh:mm:ss")       '2
    
        .Cells(2, 14).Value = Format$(Now, "hh:mm:ss")
        '.Cells(2, 17).Value = Format$(Now, "hh:mm:ss")
        .Cells(2, 19).Value = Format$(Now, "AM/PM")
        .Cells(6, 19).Value = Format$(Now, "ss")
    
    End With
    
   ' Call StartTimer to schedule the procedure again
   StartTimer
End Sub

As you can see from the above code, this new macro is removing this line of code : '.Cells(2, 17).Value = Format$(Now, "hh:mm:ss")
There is no need to leave that line in the code. You can delete it.

In Q2 ... remove any FORMATTING to the cell.

In Q2 ... paste this formula : =MINUTE($B2)

You should be good to go !

Just to be certain, here is the complete new code :

Option Explicit

Public RunWhen As Double
Public Const cRunIntervalSeconds = 1 ' 1 seconds
Public Const cRunWhat = "The_Sub"  ' the name of the procedure to run


Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
End Sub
Sub The_Sub()
Application.Cursor = xlNorthwestArrow                   'prevents mouse cusor blinking

With ActiveSheet
        .Cells(2, 6).Value = Format$(Now, "mm/dd/yyyy") '1
        .Cells(2, 2).Value = Format$(Now, "hh:mm:ss")       '2
    
        .Cells(2, 14).Value = Format$(Now, "hh:mm:ss")
        .Cells(2, 19).Value = Format$(Now, "AM/PM")
        .Cells(6, 19).Value = Format$(Now, "ss")
    
    End With
    
   ' Call StartTimer to schedule the procedure again
   StartTimer
End Sub

Sub StartBlink()
  Dim xCell As Range
  Dim xTime As Variant
 
  Set xCell = Range("P2")
 
  With ThisWorkbook.Worksheets("Sheet1").Range("P2").Font
    If xCell.Font.Color = vbRed Then
        xCell.Font.Color = vbWhite
    Else
        xCell.Font.Color = vbRed
    End If
  End With
 
  xTime = Now + TimeSerial(0, 0, 1)
  Application.OnTime xTime, "'" & ThisWorkbook.Name & "'! StartBlink", , True

End Sub

Remember to make the changes to Cell Q2

 

[Ark68]

Logit, thank you soooo much!! All the gremlins have been evicted and things are purring away smoothly. I very much appreciate your help.

 

[Logit]

.
You are welcome. Glad to help.