display current time on Worksheet Activate

[robertvdb]

how can I get (with VBA) the current time in cell A2, and have it updated each time the sheet is activated ?

Thanks.

 

[Georgiboy]

If this is to happen on one sheet then try the below inside the sheet module:

Private Sub Worksheet_Activate()
    Range("A2") = TimeValue(Now())
End Sub

If it is to happen on all sheets then try the below inside the workbook module:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Range("A2") = TimeValue(Now())
End Sub

 

[robertvdb]

Thanks Georgiboy. Would it also be possible to adapt your code, so that the FORMULA shows in A2 ?

Like range("A2").formula = ...

 

[Georgiboy]

Yes but if it is a formula, the time will update whenever the sheet is calculated, would that be an issue?

 

[Georgiboy]

You could just have the formula:

=NOW()

In cell A2 and format it as time?

 

[robertvdb]

updating whenever the sheet is calculated is not an issue, so feel free. I have to rush to a meeting now, so take your time

 

[Georgiboy]

Maybe the below:

Private Sub Worksheet_Activate()
    Range("A2").Formula = "=--TEXT(NOW(),""hh:mm:ss"")"
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Range("A2").Formula = "=--TEXT(NOW(),""hh:mm:ss"")"
End Sub

 

[robertvdb]

Thanks Georgiboy ! Works perfectly.

I just wonder why the two "--" at the beginning of your formula.

 

[Georgiboy]

They convert the text result into a numerical value. In the same way the VALUE() function works. If you remove them you will still get a time but as text in the cell.

 

[robertvdb]

Thanks !