Userform Listbox Flickers when updating.

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
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

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

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

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

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

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows
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

VBA 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.
VBA Code:
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
VBA Code:
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,
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,467
Messages
5,624,913
Members
416,064
Latest member
PaulBr2

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
Top