listbox flickering when updating from sheet

[Sharid]

I have two issues with a listbox.

1) I have a userform, which has a multipage on it, on the multipage there is a listbox. The listbox shows the results from sheet2, when the code is run, the sheet is populated and the results show in the listbox. As the update is rapid, the list box flickers. I have tried to add, but this is not having any luck and the flickering is still there.

Application.ScreenUpdating = False
     'My code
Application.ScreenUpdating = True

Does anyone know if there is a way to stop the flickering?

2) As the list box is updating I can not scroll up or down or across, until it has finished populating. Have I missed something in the listbox propeties?

Thanks

 

[dmt32]

Hi,
would probably be more helpful to forum if you could share all the code you have that populates your listbox

Dave

 

[Sharid]

Thanks for the update. Here is the code.

 With UserForm1.ListBox3
      .ColumnCount = 12
      .ColumnWidths = "160;190;70;70"
        .RowSource = "'" & Sheet2.Name & "'!$A$1:$L$" & Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).Row
   End With

I have had a few issues with the code as I was not 100% sure where to place it.

I tried it in the UserForm Initilize , however the Listbox is on a nested multipage and it is not the first listbox, nor on the first page, so it would not update as the data went into the sheet, once the process was finished. I had to close the for and the repoen it populate the listbox. I then placed it into the tab of the multipage so that when that page was active it would update. that did not help, similar issue. I finally placed the code into the Sheet change event for sheet 2 and that would update the listbox but flickered a lot.

Currently the its in the sheet change event

 

[dmt32]

Placing code in worksheet change event may explain the flickering

Your code should work ok in the UserForm_Intialize event

Private Sub UserForm_Initialize()
    With Me.ListBox3
      .ColumnCount = 12
      .ColumnWidths = "160;190;70;70"
        .RowSource = "'" & Sheet2.Name & "'!$A$1:$L$" & Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).Row
   End With
End Sub

or you can qualify it to the the multipage

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

You will need to change the page number as required but be mindful pages start at 0 (zero)
Also, do not make to common error of renaming this event to match your userform name

Hope helpful

Dave

 

[Sharid]

Thanks for this. However I still have the same problem. This is where the result DO NOT show in the listbox as the sheet is updated, this is why I placed the code onto the sheet2 change events as that was the only work around, however I then have flickering.

 

[dmt32]

Thanks for this. However I still have the same problem. This is where the result DO NOT show in the listbox as the sheet is updated, this is why I placed the code onto the sheet2 change events as that was the only work around, however I then have flickering.

In that case can only suggest you trying disconnecting the RowSource before you perform the Range update

UserForm1.ListBox3.RowSource = ""
     'perform range update
     
    With UserForm1.ListBox3
      .ColumnCount = 12
      .ColumnWidths = "160;190;70;70"
      .RowSource = "'" & Sheet2.Name & "'!$A$1:$L$" & Sheet2.Cells(Sheet2.Rows.Count, 1).End(xlUp).Row
   End With

Or replace using RowSource with the List property of the control

With UserForm1.ListBox3
      .ColumnCount = 12
      .ColumnWidths = "160;190;70;70"
      .List = Sheet2.Range("A1").CurrentRegion.Value
   End With

and see if any of these changes help

Dave

 

[Sharid]

I placed the first code in Userform Initialize and still had the same issue. I moved it to the Sheet Change Event and still had flickering.
As for the second code I get this error message

 

[dmt32]

I suspect with that error you have not copied the suggest code fully

.List = Sheet2.Range("A1").CurrentRegion.Value

Must have Value on the end of the line of code
Also note, CurrentRegion will not work if sheet is protected

Dave

 

[Sharid]

Sheet2 is Unprotected

Q) The form opens up on Sheet1, but Data comes from Sheet2 could that be the issue.

 

[dmt32]

Sheet2 not being active should not be a problem

Are you able to place copy of your workbook in a dropbox & provide link to it?