Excel Userform Listbox Flickering

0
votes

Is there a way to stop a Listbox from flickering when it is updating with data?

I have a listbox on a Multipage on a userform, when the data goes into the sheet it shows in the listbox. However as the data goes into the sheet the listbox flicker, which become a bit annoying.

In my main code, which is a FOR LOOP, i have tried Application.ScreenUpdating = False and set it back to true at the end, but nothing seems to work. I can't seem to find the answer on google.

This is my code, which is in the sheet change event. I have NO code in the listbox

Private Sub Worksheet_Change(ByVal Target As Range)
    With ExcelForm.UrlsListBox1
      .ColumnCount = 1
      .ColumnWidths = "600"
      .RowSource = "'" & Sheet2.Name & "'!$A$1:$b$" & Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).row
   End With
End Sub

FOR INFO - My user form has several multipages and each page has a listbox each has its own sheet. So I can not just place this code in form initialize, as it has to be relevent to that multipage + listbox when the tab is selected.

This is an updated code not 100% sure if this is what Zac was advising on

Private Sub Worksheet_Change(ByVal Target As Range)
  With ExcelForm.UrlsListBox1
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
     .RowSource = "'" & Sheet2.Name & "'!$A$1:$b$" & Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).row
   End If
  End With
End Sub

The Listbox flicking as NOT stopped

Also Posted on Mr Excel Today at 1:51pm uk time Link

2
excelvba
Your list box is being updated everytime something changes in the sheet. Check if the change is in relevant range (you can use Intersect for that). Also, try commenting out ColumnCount and ColumnWidths lines – Zac
Zac, I have done as you have sugested but the flickering is still there. I had to put the code into the sheet change event otherwise the listbox would not update as the data went in. Do you know of any other method? – Sharid
Can you show your updated code? – Zac
this is it Private Sub Worksheet_Change(ByVal Target As Range) With ExcelForm.UrlsListBox1 ' .ColumnCount = 1 ' .ColumnWidths = "600" .RowSource = "'" & Sheet2.Name & "'!$A$1:$b$" & Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).row End With End Sub – Sharid
sorry, but I can't get it to show as a block of code – Sharid

2 Answers

0
votes

As per the feedback, setting Application.ScreenUpdating=False dosent work.

I have modified your code to clear ListBox before it updates. Hopefully this might work.

Private Sub Worksheet_Change(ByVal Target As Range)
  With ExcelForm.UrlsListBox1
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
     .Clear
     .RowSource = "'" & Sheet2.Name & "'!$A$1:$b$" & Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).Row
   End If
  End With
End Sub
0
votes

This is the work around I am using to fix the ListBox flickering. Full answer is on Mr Excel. Link is above, in my original post, where I have stated that this is also posted on that forum.

Will slow down the code a bit, but works to reduce or stop the flickering

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As Double
With ExcelForms.UrlsListBox1
     .ColumnCount = 1
     .ColumnWidths = "600"
      .RowSource = "'" & Sheet2.Name & "'!$A$1:$b$" & Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).row

t = Timer
Do Until Timer > t + 0.8
Loop

End With
End Sub

The timer slows down the rate of data being pasted into sheet2, the Listbox then reduces or stops to flicker depending on t + 0.8 , the higher the number the slower the code, the less the flickering. This is only a work around. If anyone knows a better way please post.