Listbox bulk refresh won’t redraw if called by Listbox click.

I’ve been playing around with ListBoxes recently. Or more to the point, they’ve been playing around with me. After much staring and swearing, I’ve found that you can’t get a ListBox to repaint itself when feeding it an entire list in one go in response to a Listbox_Click event in that same ListBox. It does however repaint correctly if you run the exact same code from somewhere else…say a CommandButton-initiated action.

By new list, I’m talking about populating the ListBox with an array via the Listbox.list=SomeArray approach. The .AddItem method works fine. So there’s always that: you can simply remove all items one by one, then add all the new items one by one. But that’s really slow: on my laptop it takes something like 15 seconds to add 10,000 items to that listbox. And it gets slower the more you add. And you can probably double that, if you need to replace each and every one of those items with something else. Whereas the Listbox.list=SomeArray approach takes less than a second to add ten times as many items. It’s fast.

So why would you want to add tens of thousands of items to a ListBox? And why would you want to perform a wholesale update that list by clicking in the Listbox itself? Here’s why: I’m designing a UserForm to help filter PivotTables. It’s a cross between the existing Pivot Filter functionality and a Slicer. Here’s what you see if you double-click on a PivotField header:

Note the Search field at the top, and the three buttons immediately below that search field. If you type something into that field, then instead of displaying everything that’s currently filtered, you instead get a list of any matches, and you can then apply those search results to the underlying PivotTable via those three CommandButtons. The first cb simply filters the PivotTable to reflect the search, and the other two let you add or remove any search result from an existing filter. (The native filter lets you add, but not remove).

I want to do away with those three command buttons, and instead (in the event that a search is performed) simply list those three options at the top of the ListBox above any search results returned. Clicking on those three options will then trigger the exact same code as currently triggered by the Command Button.

Here’s how that looks currently if I actually type something in that Search box (Note I haven’t yet removed the three command buttons this new functionality will make redundant from that Search frame):

I’ve added a simple bit of code to the lbResults_Change() event handler that checks if a user clicks any of those first three options. All that code does is trigger the exact same routines as would be triggered if they’d simply clicked on the actual command buttons themselves:

For i = 0 To 4
    If Me.lbResults.Selected(i) Then Exit For
Next i
Select Case i
    Case 0: cmdApplySearch_Click
    Case 1: cmdAddToFilter_Click
    Case 2: cmdSubtractFromFilter_Click
    Case 3: Me.lbResults.Selected(3) = False
End Select

For i = 0 To 4

If Me.lbResults.Selected(i) Then Exit For

Next i

Select Case i

Case 0: cmdApplySearch_Click

Case 1: cmdAddToFilter_Click

Case 2: cmdSubtractFromFilter_Click

Case 3: Me.lbResults.Selected(3) = False

End Select

The listbox gets updated just fine if I click on the command buttons, as you can see from the below. The Pivot has been filtered accordingly, and those contextual search options have been removed from the top of the listbox (and the search field cleared):

But watch what happens if I trigger the exact same code by clicking that first option in the ListBox itself:

As you can see from the above screenshot, it still shows those three options at the top, even though they are NOT in the array that I assigned to the listbox, as evidenced by the screenshot below:

The Listbox DID get updated, mind:

? .ListCount 3
? .List(0) 263213: ICT Systems Test Engineer
? .List(1) 263299: ICT Support and Test Engineers nec
? .List(2) 839313: Product Tester

? .ListCount 3

? .List(0) 263213: ICT Systems Test Engineer

? .List(1) 263299: ICT Support and Test Engineers nec

? .List(2) 839313: Product Tester

…It just didn’t get redrawn. And it won’t get redrawn unless I refill the ListBox via one of those CommandButtons I’m trying to do away with. And it doesn’t seem to matter if I set the focus to the CommandButton before I try to refresh the Listbox, or even clear the ListBox entirely before I try to refresh it.

You can see this behavior for yourself in this sample file I’ve prepared.

When the userform opens, you’ll see this:

Clicking the CommandButton correctly loads a new array into the ListBox (and increases the size of the ListBox accordingly):

…but if you click in the ListBox itself – which triggers the exact same code – then while you’ll see that the ListBox got expanded, it did not get redrawn:

Just as weird: you can see that the ListBox got expanded by two lines. That’s because the ListBox_Click event got executed twice…even though I have the requisite event suppression code in place (and I’m not talking about application.enable events here, because that doesn’t work for UserForm events). Putting a breakpoint in the code also shows that the 2nd time it runs occurs immediately after the previous run finishes, and not as a response to the .list = SomeList bit that normally triggers such repeat runs.

And if I now click that CommandButton, you can see that the missing numbers from the double ListBox_Click pass are in fact there, as well as the extra addition from the latest CommandButton_Click:

If I click that Use .AddItem Approach radio button and then click in the ListBox, things go according to plan:

So there’s always that approach. But that approach sucks. Maybe I’ll be forced to keep those CommandButtons in the UserForm after all. Anyone else experience this issue, or have a workaround up their sleeve?

11 thoughts on “Listbox bulk refresh won’t redraw if called by Listbox click.”

Ah, what an idiot I am for overlooking the painfully obvious solution. The ListBox list happily redraws in response to any event other than the ListBox_Click event, right? So all I needed to do was to use the ListBox_Click event to determine what got clicked (as I currently do), and then use the ListBox_MouseUp event to trigger the actual updating of the ListBox list. Works a treat.

Hopefully this post will save someone else two days of pain in the future. Probably me.

Can you explain in more detail the MouseUp solution? I have not been able to make it work. This issue is driving me crazy – Thanks

John: Sorry, been away on holiday and just got back. You still having problems? If so, can you elaborate further what it is you are trying to do?

Hello Friend,

I am using Listbox in the last day and I’m facing the SAME problems.
The listbox only accepts the first batch using the “.List = SomeVariantArray”.
When I tried reload the Listbox, it doesn’t refresh no matter what I do.

Do you can solve this problem?
Best regards

Hi Eduardo. See the comment above use the ListBox_Click event to determine what got clicked (as I currently do), and then use the ListBox_MouseUp event to trigger the actual updating of the ListBox list.

Thank you. This one was driving me crazy. A nice, simple solution to yet another Excel bug.

Refreshing a listbox from an array doesn’t properly update the list count in the listbox code. For example, load the initial array into a listbox, then add 1 item to the end of the array and reload the array into the listbox. The item added will not show in the listbox even though it is in the array. Add a second item to the end of the array and reload the array into the listbox again, and now the first item added will show up in the listbox, but not the second item. Add a third item to the end of the array and reload the array into the listbox again, and now the listbox will show the the first and second items added, but not the third item. Another frustrating Excel listbox bug.

That sounds like the array is using the wrong base, like 1 to 10 instead of 0 to 9. But I just tried it using 1-based and 0-based and it worked for both. Here’s the zero based example.

Private Sub CommandButton1_Click()
    
    Dim a() As Long
    Dim i As Long
    
    If Me.ListBox1.ListCount = 0 Then
        ReDim a(0 To 9)
        For i = 0 To 9
            a(i) = i
        Next i
        Me.ListBox1.List = a
    Else
        ReDim a(0 To Me.ListBox1.ListCount)
        For i = 0 To Me.ListBox1.ListCount
            a(i) = i
        Next i
        Me.ListBox1.List = a
    End If
    
End Sub

Private Sub CommandButton1_Click()

Dim a() As Long

Dim i As Long

If Me.ListBox1.ListCount = 0 Then

ReDim a(0 To 9)

For i = 0 To 9

a(i) = i

Next i

Me.ListBox1.List = a

Else

ReDim a(0 To Me.ListBox1.ListCount)

For i = 0 To Me.ListBox1.ListCount

a(i) = i

Next i

Me.ListBox1.List = a

End If

End Sub

Until today, nothing has worked for me. Me.repaint completely useless. DoEvents useless too. I didn’t mass-load the list box; I loaded them one by one like Dick, a la

For i = lstBox1.ListCount - 1 To 0 Step -1
      lstBox1.List(i) = sMyArray(i + 1)
      If sMyArray(i + 1) = vSelected Then vDesired = i
  Next i
      lstBox1.ListIndex = vDesired

For i = lstBox1.ListCount - 1 To 0 Step -1

lstBox1.List(i) = sMyArray(i + 1)

If sMyArray(i + 1) = vSelected Then vDesired = i

Next i

lstBox1.ListIndex = vDesired

The list box IS correctly populated, but displays incorrectly (needs painting). I could “correct” the display by going Control-End then Control-Home but that’s ridiculous (and loses the selected item).

So here is my idiotic solution that has worked 100%, and the absence of which fails 100%: just repeat the loop after setting .ListIndex.

Perform the loop to load it (shown above).
lstBox1.ListIndex = vDesired
REDO the very same loop.

It’s soooo stupid but the list box displays correctly. Using 16/365.

I’m sorry that I puked the code tags above but am unable to edit it, if someone would kindly slip in the correct tags.

I also neglected to add an explanatory statement in anticipation that someone would look at that sequence (loop, then set .ListIndex, then loop again) and wonder why I didn’t set .ListIndex again after the silly repeated loop that makes painting work. But if you think about it, .ListIndex does not need to be set again. Reloading the control items as such doesn’t alter that setting.