Userform Flickers While Looping Through and Updating Controls

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
437
So I have a bunch of controls on a page - 140 image controls (without the images) whose backcolor changes from vbGreen to vbRed based on some criteria. Code works, things turn different colors, all is great! Only I can see flickering of the controls as the code runs. Actually posting the code is not the important thing (figured it might provide context) - I'm using these image controls just as a rectangular shape.

code portion
VBA Code:
'Change colors of image controls
For Each Ctrl In Me.Controls
    If Left(Ctrl.Name, 3) = "FP4" And Ctrl.Tag = "Plate" Then
        'Find the row of the plate position
        lRow = WorksheetFunction.Match(Right(Ctrl.Name, 3), rRange, 0)
        'Here is the criteria to decide contol backcolor
        If wSht.Cells(lRow, rRange.Column + 1) > CLng(Me.FP4_AgeTB.Value) Then
            Ctrl.BackColor = vbRed
        ElseIf wSht.Cells(lRow, rRange.Column + 1) <= CLng(Me.FP4_AgeTB.Value) Then
            Ctrl.BackColor = vbGreen
        End If
    End If
Next Ctrl

The code I'm interested in follows below. Found it on a different site. I was curious as to how it works. Does the entire code base get posted in a class called Freeze? Also the author mentioned you call a class Freeze as follows:
I guess the bottom line is that I'm just not sure how it all works.

VBA Code:
Dim Freeze as New Freeze
Freeze.Form Me

Class Code?

VBA Code:
'declare function to stop window flickering when loading / updating forms
Private Declare Function LockWindowUpdate Lib _
"USER32" (ByVal hwndLock As Long) As Long

Option Explicit

Public Sub Form(pForm As Form)
LockWindowUpdate pForm.hWnd
End Sub

Private Sub Class_Initialize()
'check who called the class
End Sub

Private Sub Class_Terminate()
LockWindowUpdate False
End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Application.ScreenUpdating = False
before updating controls and
Application.ScreenUpdating = True
after update
 
Upvote 0
Sorry, probably should have mentioned that Application.ScreenUpdating does not work in these cases. I'm more targeted on how the above code is implemented.
 
Upvote 0
Basically you are using the USER32 dll file API LockWindowUpdate to attach to a particular form for freezing a window during drag/drop

Read about it here

might be better way to call your code and have it not flicker? where do you place those lines matters and how often are you forcing a redraw/refresh?
 
Upvote 0

Forum statistics

Threads
1,215,471
Messages
6,125,000
Members
449,202
Latest member
Pertotal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top