Autofiltering dropdown menus on userform from external database

**Solaris** · 07-17-2012, 09:28 AM

Hi!

I'm working on a project where I have an external database in the form of an excel sheet, from which I want to extract data based on user choices. To increase efficiency the data shall only be loaded once from the database, and then be temporarily stored locally. For security reasons the bulk of data shall be invisible to the user. The two ways I've been thinking of doing this is either storing the database in an array or copying it to an inaccessible worksheet.

I then want to apply an autofilter on certain search parameters to make the program as user friendly as possible. Is this even possible to do if the data is stored in an array? I want to place the dropdown menus for doing selections on a userform, and when an option is selected in one I want the amount of choices in the other ones to be reduced as per a normal autofilter. So basically I want to make an autofilter with the header row menus on a userform and the data invisible to the user.

I haven't been able to figure out how to do this, so any help would be greatly appreciated.

Cheers!

**Tinbendr** · 07-24-2012, 10:11 AM

We need a sample workbook to work out the details.

Use one of the sheets to simulate the database. Tell us how big the dataset could be.

Create the userform and how ever many controls needed to satisfy your requirement.

The more specific info you give us, the better we can help you.

You can attach the workbook here. Look under Go Advanced, scroll down to manage attachments.

**Solaris** · 07-25-2012, 07:15 AM

Hi David, thanks for answering!

I guess I could do that if needed, but it will probably be simpler if I simplify my explanation instead.

So basically, what I want to do is imitate the autofilter function in excel, but with the data stored in an array and the controls on a userform.

Is this possible to do? With efficiency in mind, how would you go about it?

Regards,
Samuel

**Tinbendr** · 07-25-2012, 07:34 AM

You can look into Excel builtin query functions to get the database into memory.

Populate the combobox with first selection.

When user selects first combobox, you iterate the array again, filtering the next level of data, and so on. That's called cascading comboboxes and there are hundreds of examples on the web.