Excel Userform Listbox Flickering.

[Sharid]

I have a userform with a multipage, on each page I have a listbox, the data for the listbox come from each sheet. The listboxes flickers when the data is updates in them. If I place a wait time in my FOR LOOP OR Sheet Change Event then the flickering goes away. The only problem is that it slows down the code a lot. I was trying to set it to millseconds, like half a second, but could not get it to work.

Please see question at the bottom of this thread. Thanks

I have also posted on Stackoverflow Here

For Loop

Do
    For Each li In HTMLdoc.getElementsByTagName("li")
        If li.getAttribute("class") = "b_algo" Then
            Set link = li.getElementsByTagName("a")(0)
            .Cells(i, 1).Value = link.getAttribute("href")

          '######  TIME DELAY SET TO 1 SECOND ######
           Application.Wait (Now + TimeValue("0:00:01"))
             'Application.Wait (Now + 0.000001) 'FOR MILLSECONDS
            i = i + 1
        End If
    Next li

Or I can place the wait time in the Sheet Change Event

Sheet Change Event

Private Sub Worksheet_Change(ByVal Target As Range)
    With ExcelForm.UrlsListBox1
      .ColumnCount = 1
      .ColumnWidths = "600"
 '######  TIME DELAY SET TO 1 SECOND ######
       Application.Wait (Now + TimeValue("0:00:01"))
       'Application.Wait (Now + 0.000001) 'FOR MILLSECONDS
      .RowSource = "'" & Sheet2.Name & "'!$A$1:$b$" & Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).row
   End With
End Sub

A One second delay slows the code down too much. I was advised on Stackover Flow to do the Sheet Change Event As an "Intersect". I have Google it, but don't know how to change my code to test if that will work, I have put an updated code on Stackoverflow, but not sure if it is correct as it did not get rid of the problem.

This was another code that I was using, but it would not update when the result would go into the sheet. It ONLY updated if I closed the form and reopened it.

Private Sub UserForm_Initialize()
With Me.MultiPage2.Pages(1).ListBox3
.ColumnCount = 1
.ColumnWidths = "600"
.RowSource = "'" & Sheet2.Name & "'!$A$1:$B$" & Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).Row
End With
End Sub

Q1) Is there a way to refresh the above code every few seconds, with a loop and application wait time?
So as long as data is going ito sheet2 it loops and refreshers one of the above codes, ensuring the CURRENT sheet data show in the listbox. This way I DO NOT have to add a wait time to my code FOR Loop to slow it down or the sheetchange event, just a refresh. The data will go in as normal and will refresh the listbox with the current data every few seconds and then end the refresh waittime loop when the data input has stopped in sheet2.

Hopefull this will stop the flickering, as the list box is updated every few seconds.

Thanks

 

[Yongle]

I do not know if the flickering will go away, but this delays code execution by 1 millisecond

Dim t As Double

'these 3 lines must be consecutive
t = Timer
Do Until Timer > t + 0.001
Loop

 

[Sharid]

Yongle

This has helped in the reduction of the list box flickering. The one second delayed the code too much, but the flickering was zero. I have increased the millseconds as 0.001 kept of flickering. I changed it to 0.5 which should be half a second, flickers slightly every now and then, but code runs faster.

Any chance you could look at the question I posted in my thread.

The sheet change event is causing the flickering, as the data is going into it too fast. The timer will slow down the code, which helps.

However if possible. if the listbox could be updated every few seconds with current sheet data, rather than the delays in the code. Some how the listbox refreshes every few seconds with the latest sheet2 data.

 

[GWteB]

I suspect that this might work in your case. It is similar to Application.ScreenUpdating but only for UserForms shown on screen. To be applied to changes to your displayed UserForm during run-time, eg dynamically adding controls or refreshing a ListBox. It is less time consuming and usually flickers of the UserForm are barely noticeable.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oFL     As clsFormLock
    Dim oUsf    As UserForm
    
    Set oUsf = ExcelForm            ' <<<< set proper reference to your UserForm
    Set oFL = New clsFormLock       ' <<<< create instance of custom class with Lock & Unlock methods
    With oUsf.UrlsListBox1
        .ColumnCount = 1
        .ColumnWidths = "600"
        oFL.FormLock oUsf           ' <<<< disable screen updating of on screen UserForm "oUsf"
        
        .RowSource = "'" & Sheet2.Name & "'!$A$1:$b$" & Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).Row
        
        oFL.FormUnLock              ' <<<< enable screen updating
   End With
End Sub

Within VBE insert a Class Module, rename it to clsFormLock and paste this:

Option Explicit

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function LockWindowUpdate Lib "user32" (ByVal hwndLock As Long) As Long

' Always Unlock when this instance goes out of scope
Private Sub Class_Terminate()
   LockWindowUpdate 0
End Sub

Public Sub FormLock(argUsf As UserForm)
   Dim hWnd As Long

   If Val(Application.Version) >= 9 Then
      hWnd = FindWindow("ThunderDFrame", argUsf.Caption)
   Else
      hWnd = FindWindow("ThunderXFrame", argUsf.Caption)
   End If
   If hWnd > 0 Then LockWindowUpdate hWnd
End Sub

Public Sub FormUnLock()
   LockWindowUpdate 0
End Sub

 

[Sharid]

GWteB, Thanks for this, any code is a help. I have tried what you have stated however, the WHOLE userform is now flickering. Unfortantally I can not post my form as it is work item and has a some senstive data on it.

I also had to change both of this rows to PtrSafe (Private Declare PtrSafe Function )

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function LockWindowUpdate Lib "user32" (ByVal hwndLock As Long) As Long

Its need to work on a 32 and 64 bit system.