Hello,
I have a UserForm which has a button called btnREFRESHDATA.
When we click the button, it runs a Sub called GETSQLDATA (found in Module1)
I want to do this:
When GETSQLDATA starts running, it changes the background color of btnREFRESHDATA to vbRed.
When GETSQLDATA finishes, it changes the background color back to silver.
------
My code below does not seem to work.
The color of btnREFRESHDATA does not change at all.
What could be wrong?
--------------------------
Sub GetSqlData()
Dim ConnectionStr As String
Dim MyRecordSet As ADODB.Recordset
Dim Source As String
UserForm1.btnRefreshData.BackColor = vbRed
ConnectionStr = "Provider=SQLOLEDB.1;Password=Passwordxxx; User ID =sa; Data Source=MYSERVER; Initial Catalog=XYXLTD"
Set MyRecordSet = New ADODB.Recordset
Source = "EXECUTE [dbo].[usp1]"
MyRecordSet.Open Source, ConnectionStr
ActiveWorkbook.Worksheets("Database").Cells.Clear
ActiveWorkbook.Worksheets("Database").Range("A1").CopyFromRecordset MyRecordSet
UserForm1.btnRefreshData.BackColor = RGB(217, 217, 217) '// Silver color
ScheduledTimeForGetSqlData = 0 '// Clear previous OnTime setting.
SetOnTimeForGetSqlData
End Sub
----------
Thanks
Leon Lai
Change color of button in User Form using module code?
-
- Lounger
- Posts: 47
- Joined: 12 Sep 2021, 14:50
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Change color of button in User Form using module code?
Does it help if you insert
below each of the bold lines?
Code: Select all
UserForm1.Repaint
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 47
- Joined: 12 Sep 2021, 14:50
Re: Change color of button in User Form using module code?
Yes!
Great! It works!
Thanks a lot
Leon Lai
Great! It works!
Thanks a lot
Leon Lai
-
- Lounger
- Posts: 47
- Joined: 12 Sep 2021, 14:50
Re: Change color of button in User Form using module code?
Hello HansV
But I have a new problem now.
Previously, the code in the button CLOSE USER FORM worked:
-------------------------------------
Private Sub btnCloseForm_Click()
Application.OnTime EarliestTime:=ScheduledTimeForGetSqlData, Procedure:="GetSqlData", Schedule:=False
Unload UserForm1
----------------------------
After adding UserForm1.Repaint, the button btnCloseForm can no longer stop the OnTime program: Even after I closed the UserForm, the add-in continues to refresh my SQL. I have to close my Excel Sheet.
--------------
What does UserForm1.Repaint do?
I suspect it reruns UserForm_Initialize(). Right?
If correct, I must modify this code.
-------------------------------
Here is my code (over-simplified) for UserForm_Initialize().
What should I modify?
-------------------------------
Public Sub UserForm_Initialize()
GetSqlData
ScheduledTimeForGetSqlData = 0
SetOnTimeForGetSqlData
Call ConvertDatabaseToArray
End Sub
--------------------------
Leon
But I have a new problem now.
Previously, the code in the button CLOSE USER FORM worked:
-------------------------------------
Private Sub btnCloseForm_Click()
Application.OnTime EarliestTime:=ScheduledTimeForGetSqlData, Procedure:="GetSqlData", Schedule:=False
Unload UserForm1
----------------------------
After adding UserForm1.Repaint, the button btnCloseForm can no longer stop the OnTime program: Even after I closed the UserForm, the add-in continues to refresh my SQL. I have to close my Excel Sheet.
--------------
What does UserForm1.Repaint do?
I suspect it reruns UserForm_Initialize(). Right?
If correct, I must modify this code.
-------------------------------
Here is my code (over-simplified) for UserForm_Initialize().
What should I modify?
-------------------------------
Public Sub UserForm_Initialize()
GetSqlData
ScheduledTimeForGetSqlData = 0
SetOnTimeForGetSqlData
Call ConvertDatabaseToArray
End Sub
--------------------------
Leon
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Change color of button in User Form using module code?
Repaint does not cause UserForm_Initialize to run, it simply forces Excel to update the userform immediately instead of waiting until the procedure ends.
You can check this by adding a line
Debug.Print "Initialize"
to the UserForm_Initialize event procedure. You should see "Initialize" only once in the Immediate window, instead of after every Repaint.
You can check this by adding a line
Debug.Print "Initialize"
to the UserForm_Initialize event procedure. You should see "Initialize" only once in the Immediate window, instead of after every Repaint.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 47
- Joined: 12 Sep 2021, 14:50
Re: Change color of button in User Form using module code?
>> Repaint does not cause UserForm_Initialize to run,
Oh! I see...
But then my problem becomes even more mysterious...
When I add the 2 Repaint, I cannot stop the OnTime program.
When I comment the 2 Repaint, then I can stop the OnTime program when I click the Close User Form button.
I am baffled. I tried commenting / uncommenting several times... nothing else!
So, where is the problem?
Any idea how I should tackle the problem?
Oh! I see...
But then my problem becomes even more mysterious...
When I add the 2 Repaint, I cannot stop the OnTime program.
When I comment the 2 Repaint, then I can stop the OnTime program when I click the Close User Form button.
I am baffled. I tried commenting / uncommenting several times... nothing else!
So, where is the problem?
Any idea how I should tackle the problem?
-
- Lounger
- Posts: 47
- Joined: 12 Sep 2021, 14:50
Re: Change color of button in User Form using module code?
I think I was mistaken.
I cannot stop the OnTime program whether I comment /uncomment the 2 Repaint.
I don't know what happened.
Must investigate harder...
Will let you know.
I cannot stop the OnTime program whether I comment /uncomment the 2 Repaint.
I don't know what happened.
Must investigate harder...
Will let you know.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Change color of button in User Form using module code?
Do you have the code to stop the OnTime in the UserForm_QueryClose event procedure?
You might also add it to the UserForm_Terminate event procedure (with On Error Resume Next above it, to prevent errors if you try to stop it more than once).
You might also add it to the UserForm_Terminate event procedure (with On Error Resume Next above it, to prevent errors if you try to stop it more than once).
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 47
- Joined: 12 Sep 2021, 14:50
Re: Change color of button in User Form using module code?
I have not yet implemented UserForm_QueryClose event
I am still struggling with the code in the userform button as foll:
----------------------------------------------
Private Sub btnCloseForm_Click()
Application.OnTime EarliestTime:=ScheduledTimeForGetSqlData, Procedure:="GetSqlData", Schedule:=False
ScheduledTimeForGetSqlData = 0
Unload UserForm1
-----------------------------------------
Once this code works, I will do the UserForm_QueryClose event.
Should be similar. Right? Wrong?
----------------------------------------
I will try both events you mentioned.
I am still struggling with the code in the userform button as foll:
----------------------------------------------
Private Sub btnCloseForm_Click()
Application.OnTime EarliestTime:=ScheduledTimeForGetSqlData, Procedure:="GetSqlData", Schedule:=False
ScheduledTimeForGetSqlData = 0
Unload UserForm1
-----------------------------------------
Once this code works, I will do the UserForm_QueryClose event.
Should be similar. Right? Wrong?
----------------------------------------
I will try both events you mentioned.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Change color of button in User Form using module code?
Code: Select all
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
On Error Resume Next
Application.OnTime EarliestTime:=ScheduledTimeForGetSqlData, Procedure:="GetSqlData", Schedule:=False
End Sub
Private Sub UserForm_Terminate()
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: Change color of button in User Form using module code?
Hello HansV
I tried your code this morning.
It works wonderfully!
What was missing in my code was UserForm_Terminate().
Once you use it, any OnTime Setting is cancelled.
One thing though which caused me a lot of problems:
Before we set an On.Time Schedule, we have to cancel the existing one first, otherwise both schedules will run.
I did not understand why the add-on refreshes every, say 2 seconds instead of every 5 seconds. Now, I understand.
You have made my day!
Best Regards,
Leon
I tried your code this morning.
It works wonderfully!
What was missing in my code was UserForm_Terminate().
Once you use it, any OnTime Setting is cancelled.
One thing though which caused me a lot of problems:
Before we set an On.Time Schedule, we have to cancel the existing one first, otherwise both schedules will run.
I did not understand why the add-on refreshes every, say 2 seconds instead of every 5 seconds. Now, I understand.
You have made my day!
Best Regards,
Leon