RawlinsCross
Active Member
- Joined
- Sep 9, 2016
- Messages
- 437
I have a launched userform that contains a label that counts (in minutes) how long the userform is open. Every minute the userform label is adjustment upwards by 1 minute. Now, there could be several of these userforms open, each counting up the time from when they were open.
I got the counting to work but I can't cancel the OnTime when I close the userform.
My strategy is as follows:
1. When you open a userform, you time stamp a time in a public property of the userform. This will be used to cancel it.
2. Form my understanding, OnTime can't be used to call methods inside a userform (am I wrong on this?) so I have to pass the userform around to the various methods.
This is what I have tried so far:
When you launch the userform:
Inside the userform module
Inside Module
I got the counting to work but I can't cancel the OnTime when I close the userform.
My strategy is as follows:
1. When you open a userform, you time stamp a time in a public property of the userform. This will be used to cancel it.
2. Form my understanding, OnTime can't be used to call methods inside a userform (am I wrong on this?) so I have to pass the userform around to the various methods.
This is what I have tried so far:
When you launch the userform:
Inside the userform module
VBA Code:
Private mdRunTime As Date
'Public Property
Public Property Let RunTime(dRunTime As Date)
mdRunTime = dRunTime
End Property
Public Property Get RunTime() As Date
RunTime = mdRunTime
End Property
Private Sub UserForm_Initialize()
Call StartDuration(Me)
End Sub
'Stopping the timer when you close the userform
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Call StopIt(Me)
End Sub
Inside Module
VBA Code:
Public Sub UpdateDuration(frm As DataEntryDetails)
'This code does the counting
Dim dStartTime As Date
Dim dNowTime As Date
Dim lDiffTime As Long
Dim frm As Object
For Each frm In VBA.UserForms
If frm.Name = "DataEntryDetails" Then
'Get start/current time of event
dStartTime = CDate(frm.lblDate.Caption) + CDate(frm.lblTime.Caption)
dNowTime = Now
lDiffTime = DateDiff("n", dStartTime, dNowTime)
End If
Next frm
Call StartDuration(frm)
End Sub
Public Sub StartDuration(frm As DataEntryDetails)
frm.RunTime = Now + TimeValue("00:01:00")
Application.OnTime frm.RunTime, "'UpdateDuration frm'"
End Sub
Public Sub StopIt(frm As DataEntryDetails)
On Error Resume Next
Application.OnTime frm.RunTime, "'UpdateDuration frm'", , False
On Error GoTo 0
End Sub