# Series of dependent comboboxes in userform that show unique values of dynamic tables

1. ## Series of dependent comboboxes in userform that show unique values of dynamic tables

Hi ExcelForummers, first time posting here ;-)

This example workbook contains a datatable, which is inputted via a userform. The datatable has 4 columns: Date, Invoice no., Loads, Tonnage. This table is dynamic, as a new row gets entered each time data is entered in the userform. A different userform (the one in the example) has comboboxes which refer to the data in the table. This userform asks the user for the Date, Invoice no., Loads and Tonnage. I want to use comboboxes so that they will advise the input based on the users previous input. The first combobox asks the user for a data, and should contain a list of all the unique dates that are stored in the table. When the first combobox is inputted, the list for the second combobox will change. The list of the second combobox should be a list of unique invoice numbers, based on the date that has already been entered. The 3rd and the 4th combobox should also show a list of unique values, based on the previously entered date and invoice no.

Example: The first combobox should advise the dates: 4-Nov-14 and 15-Nov-14. User chooses 4-Nov --> second combobox should advise unique invoice numbers based on chosen date: 1252 and 1311. User chooses 1152 --> 3rd combobox should advise unique values based on previous values: 3, 8 and 7. 4th combobox: 57, 23 and 47.

I hope this explanation is good enough!

2. ## Re: Series of dependent comboboxes in userform that show unique values of dynamic tables

Give this a try.

3. ## Re: Series of dependent comboboxes in userform that show unique values of dynamic tables

Why didn't you check the solutions in this crossposted thread ?

http://www.ozgrid.com/forum/showthre...103#post707103

4. ## Re: Series of dependent comboboxes in userform that show unique values of dynamic tables

Hi,

I posted this question on different forums simultaneously. Sorry if this is not appreciated. I posted it on here, on Ozgrid (http://www.ozgrid.com/forum/showthre...d=1#post707174), and to some degree on chandoo (http://chandoo.org/forum/threads/dyn...useform.15321/)

As I am still new to the VB, I thought it would be great to have different approaches to this problem. Now I have, and I can try it myself. Thanks again for the input, you have really helped me!

5. ## Re: Series of dependent comboboxes in userform that show unique values of dynamic tables

Hi Tinbendr,

Does this VBA Code for dependent cascading work in excel 2007? I am currently working on excel 2013 version and it works fine. But I will need to make it work on other computer that has excel 2007 version. I very much appreciate your input. Thanks very much.

6. ## Re: Series of dependent comboboxes in userform that show unique values of dynamic tables

Originally Posted by Tinbendr
Give this a try.
Hi Tinbendr,

Does this VBA Code for dependent cascading work in excel 2007? I am currently working on excel 2013 version and it works fine. But I will need to make it work on other computer that has excel 2007 version. I very much appreciate your input. Thanks very much.

7. ## Re: Series of dependent comboboxes in userform that show unique values of dynamic tables

If memory search me, I wrote that in Excel 2007.

8. ## Re: Series of dependent comboboxes in userform that show unique values of dynamic tables

I've used the code and the final box doesn't seem to restrict the values? - it seems to provide details of ALL rows that equal the earlier combo box (this results in invalid combinations).. does anyone have any idea how to fix?? - or any advice?.. code is below:

``Please Login or Register  to view this content.``
In other words - I guess I'm trying to replicate filtering a spreadsheet... i.e. as you filter on each column from left to right - the only valid options for the 5th combobox should be based on the entries in the preceeding 4 comboboxes.... does anyone know how to achieve this?!?...

9. ## Re: Series of dependent comboboxes in userform that show unique values of dynamic tables

can what i'm asking be achieved?!... i "think" it can... i just don't know how... i usually find answers by searching - but couldn't find a specific answer to help with this one... (hence why it's my first post - although i've been a member for 4 YEARS!! LOL!)...

10. ## Re: Series of dependent comboboxes in userform that show unique values of dynamic tables

Untested, but I think in the If statement, you should include all previous combobox, so for example for Private Sub ComboBox2_Change(), it should be:
``Please Login or Register  to view this content.``
do the same logic for other combobox.

11. ## Re: Series of dependent comboboxes in userform that show unique values of dynamic tables

pangster,Akuini

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

12. ## Re: Series of dependent comboboxes in userform that show unique values of dynamic tables

Deleted........... didn't notice that this is such an old thread.

13. ## Re: Series of dependent comboboxes in userform that show unique values of dynamic tables

Originally Posted by Akuini
Untested, but I think in the If statement, you should include all previous combobox, so for example for Private Sub ComboBox2_Change(), it should be:
``Please Login or Register  to view this content.``
do the same logic for other combobox.
Hi Akuini! - thanks for your reply.. I just wanted to confirm that the above solution *FIXED* my issue and added in the appropriate restrictions.. It seems simple now that I know what the answer is!!! - I did a TON of searching before posting and saw that others had experienced some similar issues (as none of the examples I've saw restricts it akin to a filter) - they only rely on the previous entry to restrict the options for the comboboxes (which can result in invalid combinations as I found it!) - anyone - I hope this post will help others who have the same issue as me!

14. ## Re: Series of dependent comboboxes in userform that show unique values of dynamic tables

Hi protonLeah - thanks for the reply... I didn't think I was hijacking the thread - I thought I was building on it.. I had used virtually the same code but then found an issue with it. Point taken though, I will post new threads in future! - sorry for any hassle.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1