Userform Listbox Flickers when updating.

[Sharid]

I thought I had this fixed this problem with a timer, but it is not working. Does anyone know how to get a listbox on a userform to stop flicker as data is input onto a sheet. I have several List boxes on Multipage1, Each page has One or Two List boxes

This list box is on Multipage1 Page 3 and the data goes into Sheet4, The code below is in Sheet 4 Change Events, Each Listbox has its own sheet. Have I placed the code in the right place, should it be in the page of the Multipage or Sheet Change?

I have used Application.ScreenUpdating = False, Nothing seams to work

Private Sub Worksheet_Change(ByVal Target As Range)
             With UserForm1.ListBox1
    'Application.ScreenUpdating = False
              Dim t As Double
                   On Error Resume Next
                    .ColumnCount = 12
                    .ColumnWidths = "200;100;75,75,75,75,75,75,75,75,75,75"
                   .RowSource = "'" & Sheet4.Name & "'!$A$1:$J$" & Sheet4.Cells(Sheet4.Rows.Count, 1).End(xlUp).row
            t = Timer
               Do Until Timer > t + 0.17 
    'Application.ScreenUpdating = True
           Loop
           End With
End If
End Sub

 

[Sharid]

I am really stuck on this. I have been trying to google the answer, but can't find anything. Does anyone know how to stop the Listbox from Stop Flickering. I also tried Form Initialize, that did not help

 

[Sharid]

Is there a way to AUTO UPDATE the listbox every few seconds? This list box is on Multipage1 Tab 3 (which is page 2 as it starts from 0) and the data goes into Sheet4. Currently I am using Sheet change events and that is causing the issue. If I could auto update the listbox every few seconds that might help with stopping the flickering.

I was trying to write an If Statement or a Case Select. So when a tab on a multipage is selected then the Listbox on that Tab will auto update every few seconds. If the tab is not selected then the updates stops. I Will need to do this for each tab on the multipage

Select Case UseForm1.MultiPage1.Value
Case Is = 0
Case Is = 1
Case Is = 2
      UserForm1.ListBox1 ' then the code to Auto Update of Listbox every few seconds. 
Case Is = 3
Case Is = 4
Case Is = 5
Case Is = 6
End Select

I googled this code, but could not full work it out on how to use it with a multipage tab

Option Explicit
Private t
Sub ShowForm()
UserForm1.Show
End Sub

Sub StartThem()
    UpdateListBoxes
    t = DateAdd("s", 5, Time) ' Change the 5 to 60 to refresh every 60 seconds.
    Application.OnTime t, "StartThem"
End Sub

Sub StopThem()
    Application.OnTime t, "StartThem", , False
    Unload UserForm1
End Sub

Sub UpdateListBoxes()
    With Worksheets("Sheet1").Cells(1).CurrentRegion
        UserForm1.ListBox1.RowSource = .Address
    End With
End Sub

 

[Sharid]

I have fixed this with the above code and a bit from me. It has reduced the flickering by about 95%, The longer the refresh seconds the less the flickering. Placed The code below in a Module Called "TimeStamp" and the Ran module in main code, as shown below.

Application.Run "TimerStamp.StartThem" When Button is clicked to run the code

Option Private Module
Option Explicit
Private t

Sub StartThem()
    UpdateListBoxes
    t = DateAdd("s", 5, Time) ' Refresh SECONDS
    Application.OnTime t, "StartThem"
End Sub

Application.Run "TimerStamp.StopThem" When the code stops or finishes the LISTBOX update stops.

Sub StopThem()
    Application.OnTime t, "StartThem", , False
End Sub

Then I used and IF statement so when Sheet is Show in Cell Sheet20 E16 This listbox code run when the StartThem is run. Could have done it via tabs, however would have been an issue if user changed Tabs

This will have more IF statements one for each sheet of the tabs + listboxes

Sub UpdateListBoxes()

If Sheet20.Range("E16").Value = "Sheet4" Then 
    With UserForm1.ListBox1
        On Error Resume Next
        .ColumnCount = 12
        .ColumnWidths = "200;100;75,75,75,75,75,75,75,75,75,75"
        .RowSource = vbNullString
        .RowSource = "'" & Sheet4.Name & "'!$A$1:$J$" & Sheet4.Cells(Sheet4.Rows.Count, 1).End(xlUp).row

'#### Reduces the Flicker a bit more  
    Dim t As Double
    t = Timer
    Do Until Timer - t >= 0.35
    Loop
  End With
End If
End Sub

Not the best, but it works,