Change color of button in User Form using module code?

Leon Lai
Lounger
Posts: 47
Joined: 12 Sep 2021, 14:50

Change color of button in User Form using module code?

Post by Leon Lai »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Change color of button in User Form using module code?

Post by HansV »

Does it help if you insert

Code: Select all

    UserForm1.Repaint
below each of the bold lines?
Best wishes,
Hans

Leon Lai
Lounger
Posts: 47
Joined: 12 Sep 2021, 14:50

Re: Change color of button in User Form using module code?

Post by Leon Lai »

Yes!

Great! It works!

Thanks a lot

Leon Lai

Leon Lai
Lounger
Posts: 47
Joined: 12 Sep 2021, 14:50

Re: Change color of button in User Form using module code?

Post by Leon Lai »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Change color of button in User Form using module code?

Post by HansV »

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.
Best wishes,
Hans

Leon Lai
Lounger
Posts: 47
Joined: 12 Sep 2021, 14:50

Re: Change color of button in User Form using module code?

Post by Leon Lai »

>> 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?

Leon Lai
Lounger
Posts: 47
Joined: 12 Sep 2021, 14:50

Re: Change color of button in User Form using module code?

Post by Leon Lai »

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.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Change color of button in User Form using module code?

Post by HansV »

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).
Best wishes,
Hans

Leon Lai
Lounger
Posts: 47
Joined: 12 Sep 2021, 14:50

Re: Change color of button in User Form using module code?

Post by Leon Lai »

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.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Change color of button in User Form using module code?

Post by HansV »

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

Leon Lai
Lounger
Posts: 47
Joined: 12 Sep 2021, 14:50

Re: Change color of button in User Form using module code?

Post by Leon Lai »

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