+ Reply to Thread
Results 1 to 14 of 14

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

  1. #1
    Registered User
    Join Date
    03-23-2014
    Location
    Fiji
    MS-Off Ver
    Excel 2010
    Posts
    3

    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!

    Please help me with this challenge!!
    Attached Files Attached Files

  2. #2
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,124

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

    Give this a try.
    Attached Files Attached Files
    David
    (*) Reputation points appreciated.

  3. #3
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    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. #4
    Registered User
    Join Date
    03-23-2014
    Location
    Fiji
    MS-Off Ver
    Excel 2010
    Posts
    3

    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. #5
    Registered User
    Join Date
    10-24-2010
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2003
    Posts
    2

    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. #6
    Registered User
    Join Date
    10-24-2010
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2003
    Posts
    2

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

    Quote Originally Posted by Tinbendr View Post
    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. #7
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,124

    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. #8
    Registered User
    Join Date
    09-11-2015
    Location
    edinburgh
    MS-Off Ver
    2013
    Posts
    4

    Exclamation 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?!?...
    Last edited by pangster; 10-19-2019 at 02:57 PM.

  9. #9
    Registered User
    Join Date
    09-11-2015
    Location
    edinburgh
    MS-Off Ver
    2013
    Posts
    4

    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. #10
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    974

    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.
    Last edited by Akuini; 10-19-2019 at 10:46 PM.

  11. #11
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,981

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

    pangster,Akuini
    Administrative Note:

    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.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

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

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,775

    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.
    Last edited by jindon; 10-19-2019 at 11:47 PM.

  13. #13
    Registered User
    Join Date
    09-11-2015
    Location
    edinburgh
    MS-Off Ver
    2013
    Posts
    4

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

    Quote Originally Posted by Akuini View Post
    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. #14
    Registered User
    Join Date
    09-11-2015
    Location
    edinburgh
    MS-Off Ver
    2013
    Posts
    4

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Two comboBoxes dependent on one another (userform)
    By fionamb83 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-20-2013, 05:18 AM
  2. [SOLVED] Building dependent comboboxes on an existing userform
    By Sway1978 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-19-2013, 11:11 AM
  3. [SOLVED] Multiple dynamic Comboboxes in UserForm
    By joehjas in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-17-2012, 03:43 AM
  4. 3 dynamic comboboxes, 2nd dependent on 1st and 3rd dependant on 2nd in VBA
    By begilhang in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-09-2010, 05:15 PM
  5. Huge dependant comboboxes question (unique value population, dynamic range etc.)
    By kuraitori in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-23-2008, 08:39 PM

Bookmarks

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