Add Select All item to UserForm ListBox (created by Leth Ross)

Hi,

I got this code from Leith that uses a UserForm ListBox to filter items and it VBA Userform ListBox load selected.xls works brilliantly. Does anyone know how I could add a "Select All" option as the first ListBox item when there are 2 or more items? Then the user can select/deselect all the items.

Thanks,

Lawrence

Very cool, AlphaFrog!

Just one bug... It seems the Scripting Dictionary does not retain the Checked All setting. If you have say 1 item checked, hit the SelectAll, close the UserForm and reopen it, the original 1 item checked is again the only item checked. It should be every item checked since you closed the USerForm when that was selected.

Is the solution to run the Scripting Dictionary on UserForm close??? Is there such a method?

Thanks for your help!

Lawrence

Please Login or Register  to view this content.

The Scripting Dictionary is not really used to store your selected items. In this case, the Scripting Dictionary is only used to avoid an error when selecting ListBox items when you Activate the UserForm. Once the listbox is populated and all the proper items are selected, the Scripting Dictionary is not used any more. You could clear it and use it again to populate a different ListBox. Or you could create a second new Scripting Dictionary if you want.

The selected ListBox values are stored in Sheet1.Range("TheListSelected"). Each time the selected items in the ListBox is changed, those changes are updated on the worksheet. If your selected items list Sheet1.Range("TheListSelected") will never have typed in values that are not included in the original ListBox list Range("TheList"), then you really don't need to use a scripting dictionary.

So the answer to your question is yes you can have more than one of these types listboxes on the userform. The code doesn't rely on the Scripting Dictionary to store the selected items and you could have more than one Scripting Dictionary if you want.

Do you really want the Align Selections with Source button. The code could automatically align the selection when the user makes the selections in the lisbox.

No. That was something Leith added and I keep it in my bag of tricks...in this test file.

Are you you teacher? That explanation of the Scripting Dictionary really hit it out of the park. I totally get it now.

Anyway, thanks again for all your help. Hoping to see what I goofed up when I look at your solution on opening the the UserForm with selections already marked as true.

Lawrence

Please Login or Register  to view this content.

Oh, now this is very cool.

The coding is cleaner and more elegant. The comments are a REAL help. AND you threw in a bonus...the ListBox1_Change code positions the selected item aligned with its source without the need for an added button/code. I (rather, ALL those who peruse this forum for solutions) now have two working options!!!

What an education today.

Cheers to the AlphaFrog!

You're welcome and thanks for the feedback.

If the two lists ("TheList" and "TheListSelected") are on two different sheets, do you want the "TheListSelected" to be a contiguous list of the selected items, or aligned with "TheList" with empty cells for non-selected items?

One simple fix would be to duplicate "TheList" on Sheet2 and name it "TheListSelected".

Please Login or Register  to view this content.

Then change the rest of the code to reference "TheListSelected" instead of "TheList"

AlphaFrog,

Yes, simple works. I'll just make a copy of the Pivot Table list of items to the same sheet as the selected items. But a contiguous list of the selected items would ideal...it would make my subsequent lookup formulas so much less complicated. Is there a way to eliminate those spaces, if any?

Lawrence

Originally Posted by skysurfer

But a contiguous list of the selected items would ideal...it would make my subsequent lookup formulas so much less complicated. Is there a way to eliminate those spaces, if any?

Please Login or Register  to view this content.

AlphaFrog,

This is great stuff. I'm going to post my completed "reference" file that I keep in my bag of tricks folder for others searching for this feature.

There is one bug...the second ListBox requires TWO mouse clicks to make an item selection. Half the time the UserForm is loaded! Very wierd. I had thought that was a file corruption issue in the large file I was adding the AlphaFrog feature to, but then the bug popped up in the reference file I had built from scratch. Wonder if it's a focus issue???

Thanks!

Lawrence

Andy,

WOW! That is a lot of programming to resolve the double click. Thank you!

One question: I see that you modified the first UserForm (adding in SetFocus for each ListBox). Does that mean that either UserForm may now used without the double click issue?

Lawrence