Option Explicit
Public gContinue As Boolean
Public gCurrentTimer As Single
Public gDisplayAddress As String 'eg C10
Private Sub UserForm_Initialize()
gContinue = True
gCurrentTimer = Timer
Call CalcTime
End Sub 'UserForm_Initialize
Private Sub CalcTime()
Dim lngTimer As Long
lngTimer = Int(gCurrentTimer)
Do While gContinue
'Loop for a second
Do While Int(Timer) - lngTimer < 1
'Allow system events, other keys and clicks to be processed
DoEvents
Loop
'Display new time
Call DisplayTime(Timer)
'Reset and start the process over
lngTimer = Int(Timer)
Loop
'Finished so unload form
Unload Me
End Sub 'CalcTime
'Assumes gDisplayAddress has been set by calling code
Private Sub DisplayTime(CurTimer As Single)
Dim sngTemp As Single
Dim strTemp As String
Dim blnCheckHours As Boolean, blnCheckMinutes As Boolean
Dim lngHours As Long, lngMinutes As Long, lngSeconds As Long
If gCurrentTimer = 0 Then gCurrentTimer = Timer
sngTemp = CurTimer - gCurrentTimer
blnCheckMinutes = True: blnCheckHours = True
Do While sngTemp > 0
If lngHours = 0 And blnCheckHours Then
blnCheckHours = False
lngHours = sngTemp \ 3600
sngTemp = sngTemp - (lngHours * 3600)
End If
If lngMinutes = 0 And blnCheckMinutes Then
blnCheckMinutes = False
lngMinutes = sngTemp \ 60
sngTemp = sngTemp - (lngMinutes * 60)
End If
If lngSeconds = 0 Then
lngSeconds = Int(sngTemp)
End If
strTemp = Format(lngHours, " 00:") & Format(lngMinutes, "00:") & Format(lngSeconds, "00")
Do While Left$(strTemp, 1) = " "
strTemp = Replace$(strTemp, " 00:", " ")
strTemp = Replace$(strTemp, " 0", vbNullString)
Loop
Exit Do
Loop
'Allow calling code to set DisplayAddress after form is loaded
If Len(Trim$(gDisplayAddress)) > 0 Then
ThisWorkbook.ActiveSheet.Range(gDisplayAddress) = strTemp
End If
End Sub 'DisplayTime