creating unique items lists for comboboxes... need a better way

Still trying to learn how to explain what I need in a simple thread title, but here's my issue:

I have code written that "works", but it's taking quite a while for my file to open and run through the "initialize" event code.

The code does the following:


1. Turns off screen updating
2. Opens another source workbook from a network folder
3. Copies 8 columns worth of data (about 6,000 rows, give or take) to Sheet4 on my workbook
4. Closes the source workbook
5. Renames and sets cell formatting on the 8 column headers (row 1, columns A:H)
6. Applies an autofilter and filters the copied data based on column A
7. Creates a list of unique values from each of 6 columns from Sheet4 on column A of 6 separate sheets, each containing a named dynamic range in column A.
8. Sets focus on Sheet1 and turns on screen updating

Sheet1 contains a userform which, upon opening, uses the 6 sheets of unique data to load the lists for 6 comboboxes. The comboboxes work in a reverse cascading fashion, where filling in the first will fill the remaining ones with the data that relates to the value entered in the first combobox, and if "n/a" is entered, then the focus moves to the next combobox in line and so on until either a job is selected or all boxes are filled with "n/a". I'm actually quite proud of the combobox functionality, as it's working exactly as I intended it to.
My issue is that it's taking a LONG time for the workbook to open and initialize, presumably because I copied and modified the same section of code 6 times to perform operation "7" above... I couldn't figure out a better way of getting the combobox lists loaded with only the unique data and no repeats.


I have attached my file in its' current state, with the portion of code that opens and references the second file neutered with (') marks... the data is already loaded, other than that the sample file should run the same for you as it does for me.

The code I have so far is below, please take a look and see if there's a better, more efficient way of getting to the end result I have here. Thanks a bunch!

Please Login or Register  to view this content.

Thanks for the suggestion, bakerman2...

I searched for using dictionaries to populate comboboxes, and found this snippet of code:

Please Login or Register  to view this content.

I will play with this code tomorrow, and try to modify it for my program.

Would it be better to duplicate the code and create a separate dictionary for each combobox ("dict1", "dict2", and so on), or clear the dictionary and reload it with new keys for each of the comboboxes?

Just saw your reply after posting mine... looks like you're using the dictionaries to create the unique list sheets, which does happen faster than my way. I was thinking that with dictionaries, I could bypass the need for creating those pages altogether, though. If the dictionaries can be created within the userform instead of the workbook itself, that could eliminate the need for those extra sheets entirely. I will try that tomorrow, and post my results. I think I can move a few lines around and apply autofilter before each dictionary is created to still end up with the lists sorted, too.


Your help has cleared up many uncertainties I had regarding dictionaries, and is much appreciated!

Originally Posted by bakerman2

You probably missed Post#3.

Yup, and post #5 while replying to post #3.

Thanks again!

Here’s another option:
This is an example of a combobox that create a unique list and also can search as you type.

Please Login or Register  to view this content.

Originally Posted by bakerman2

OK, then try this one.

Code looks brilliant, better than what I was going to try for sure.

I reactivated the code for pulling from the network file, and all is working as it should, but the button click event is throwing this error on the line "New_Entry.Show":

Please Login or Register  to view this content.

Nothing changed as far as the userform name, so I'd think it must be something within the userform code preventing it from opening. Any ideas?

It seems to be erroring out on this line:

Please Login or Register  to view this content.

I added the reference to "mscorlib.dll", but that didn't help. I am on a 64 bit Windows system, so that may be the issue based on some of the comments in the linked thread.

Can the {CreateObject("System.Collections.ArrayList")} be changed to a dictionary instead of a collection and still do the same thing?

Gotcha.

But, if we apply an autofilter on each column of data before using that column to populate the dictionary, basically "pre-sorting" the data, then the dictionaries would be created in the right order... would that be a significant drain on resources?

If so, I don't mind if the data in the dictionaries or the combobox lists are not sorted, as long as the separate code that adds "n/a" as an option still adds "n/a" at the top. The rest is pretty much irrelevant, as my users will be typing their entries based on the information they have on a hard copy printout in front of them anyway.

That seems to be working fine. Thanks a bunch, you've saved me hours of trial and error, if not days!

I can't thank you enough!