How to stop an OnTime sub

[Leon Lai]

Hello,

I have a UserForm as an Excel add-in.
There is one button called REFRESH, and another button called CLOSE USER FORM

1. The REFRESH BUTTON - what it does:
(a) It runs an SQL Query and uses it to populate a worksheet.
(b) Then every (say 5 seconds), it runs the SQL Query again.
This button works fine.

------
2. The CLOSE USER FORM BUTTON - what it is supposed to do:
(a) It closes the User Form.
(b) It stops the OnTime procedure, so that the add-in stops running the SQL Query.

I have tried unsuccessfully to make this button work. Even after I closed the Form, the add-in continues to run the SQL at regular intervals. It never stops!

What's WRONG?
----------

Here are some of my relevant codes:
---------


Private Sub btnRefreshData_Click() '// In UserForn

GetSqlData
SetOnTimeForGetSqlData
End Sub
-------

Sub GetSqlData() '// In Module

Dim ConnectionStr As String
Dim MyRecordSet As ADODB.Recordset
Dim Source As String

ConnectionStr = "Provider=SQLOLEDB.1;Password=Password.1; User ID =sa; Data Source=MYSERVER; Initial Catalog=XYZLTD"

Set MyRecordSet = New ADODB.Recordset

Source = "EXECUTE [dbo].[usp1]"

MyRecordSet.Open Source, ConnectionStr

ActiveWorkbook.Worksheets("Database").Range("A1").CopyFromRecordset MyRecordSet

SetOnTimeForGetSqlData

End Sub

---------------------

Public ScheduledTimeForGetSqlData As Double '// In Module

Public Sub SetOnTimeForGetSqlData() '// In Module

ScheduledTimeForGetSqlData = Now + TimeSerial(0, 0, 5) '// secs delay
Application.OnTime ScheduledTimeForGetSqlData, "GetSqlData"

End Sub


===============================================
Below is the code for the CLOSE USER FORM button:
================================================

Private Sub btnCloseForm_Click() '// does not work

Application.OnTime EarliestTime:=ScheduledTimeForGetSqlData, Procedure:="SetOnTimeForGetSqlData", SCHEDULE:=False
Unload UserForm1
End Sub


-----------
Any help much appreciated.
Thanks
Leon Lai

[HansV]

The procedure used in OnTime is GetSqlData, not SetOnTimeForGetSqlData. So change the line

Application.OnTime EarliestTime:=ScheduledTimeForGetSqlData, Procedure:="SetOnTimeForGetSqlData", SCHEDULE:=False

to

Application.OnTime EarliestTime:=ScheduledTimeForGetSqlData, Procedure:="GetSqlData", Schedule:=False

[Leon Lai]

Hello HansV

Thanks a lot for your reply. It works fine.

I have still a small problem:

I put the foll. code in my button CLOSE USER FORM:

-----
Private Sub btnCloseForm_Click()
Application.OnTime EarliestTime:=ScheduledTimeForGetSqlData, Procedure:="GetSqlData", Schedule:=False
Unload UserForm1
End Sub
------
It works fine!

However, there is another way to close the UserForm: by clicking the X.
I will need to put a similar code in the event handler.
What is the name of the event (when you click the X)?

Thanks
Leon

[HansV]

You could do the following:

Code: Select all

Private Sub btnCloseForm_Click()
    Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    On Error Resume Next
    Application.OnTime EarliestTime:=ScheduledTimeForGetSqlData, Procedure:="GetSqlData", Schedule:=False
End Sub

[Leon Lai]

Thanks again for your generous help, HansV.
Have a nice day!

Leon