Real Time Clock Encountering Error Upon Userform Close

Greetings!

I made a userform for our daily work routine, I have added a real time ticking in the userform, upon opening the form, the clock ticks every second, but the problem is, when I close the form, an error is prompting and says "Method 'OnTime' of object '_Application' failed". Can anyone guess how to correct the code and add code so that when I close the form, the initialization of the clock stops too.

here is my workbook

Querier.xlsm

Hi Stonesteel15,

Remove the code for your Clock from the UserForm_Initialize event and place it in UserForm_Activate. That would include the reference to Module5 - GetTime.

Also see This Thread: http://www.excelforum.com/excel-prog...e-and-tme.html

Thank you for the suggestion Sir Winon, I tried to do what you have said, and it was a success that I can close the userform without having a prompt of error, however, even if I can close the form, the clock still runs in the background, I wanted the clock to stop ticking when the userform was closed. can you suggest a code, I already tried the other codes inthe link given, but it only hides the form and does not stop the ticking of clock. I tried also the userform_queryclose, userform_deactivate but no avail.

Private Sub UserForm_Activate()
    Clock = Format(Now, "mm/dd/yyyy hh:mm:ss AMPM")
    m_lasttime = Now + TimeValue("00:00:01")
    Application.OnTime m_lasttime, "GetTime"
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = 0 Then Cancel = True
theend:
End Sub

Private Sub UserForm_Deactivate()
    Clock = Format(Now, "mm/dd/yyyy hh:mm:ss AMPM")
    m_lasttime = Now + TimeValue("00:00:01")
    Application.OnTime m_lasttime, "GetTime"
End Sub

I got it, the problem was solved. I put this code below the module of GetTime()

Public Sub StopTimer()
On Error Resume Next
ld = Now + TimeValue("00:00:01")
Application.OnTime ld, Procedure:="GetTime", Schedule:=False
End Sub

Then inserted to the buttons of confirmation and cancel

Public Sub cmdClose_Click()
Application.Run "StopTimer"
Unload Me
End Sub

Private Sub cmdSelect_Click()
        Sheets("Group SR").Range("N1") = ComboBox1.Value
        Sheets("Group SR").Range("O1") = ComboBox2.Value
    If ComboBox2.Value = "ALL OWNERS" Then
        'MsgBox "Set to Per Group SR Query", vbInformation + vbOKOnly, "SR Query Confimation"
    If ComboBox1.Value = "ALL STATUS" Then
        Sheets("Group SR").Range("N1") = ""
    End If
    Else
        Sheets("Group SR").Range("L1,N1") = ""
        MsgBox "Set to Per Owner SR Query", vbInformation + vbOKOnly, "SR Query Confimation"
    End If
    Application.Run "StopTimer"
    Unload Me
End Sub

Hi Stonesteel15,

That is unfair, I was working hard at it and came up with what you did before I could give you the solution. Congratulations, I am happy that you could solve the problem!

What I have is very much what you did, i.e.

Sub StopClock()

On Error Resume Next
ld = Now + TimeValue("00:00:01")
Application.OnTime ld, "GetTime", Schedule:=False
End Sub

Good going!

ahaha, it looks exactly the same, I'm sorry Winon, I thought no one's gonna help me. But actually there's a lot of code I had to remove before the code works, and also I had to modify each of this code [Format(Now)] into [Strings.Format(Now())] to run, since it always prompting in the other pc an error of library or object not found etc etc.Anyways, I'm still waiting in the other thread I made. I am hoping you could help me on this one.

http://www.excelforum.com/excel-prog...mmon-word.html