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
How to stop an OnTime sub
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to stop an OnTime sub
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
Application.OnTime EarliestTime:=ScheduledTimeForGetSqlData, Procedure:="SetOnTimeForGetSqlData", SCHEDULE:=False
to
Application.OnTime EarliestTime:=ScheduledTimeForGetSqlData, Procedure:="GetSqlData", Schedule:=False
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 47
- Joined: 12 Sep 2021, 14:50
Re: How to stop an OnTime sub
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
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
-
- Administrator
- Posts: 78625
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How to stop an OnTime sub
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
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 47
- Joined: 12 Sep 2021, 14:50
Re: How to stop an OnTime sub
Thanks again for your generous help, HansV.
Have a nice day!
Leon
Have a nice day!
Leon