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:
If Me.lbResults.Selected(i) Then Exit For
Select Case i
Case 0: cmdApplySearch_Click
Case 1: cmdAddToFilter_Click
Case 2: cmdSubtractFromFilter_Click
Case 3: Me.lbResults.Selected(3) = False
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
…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?