+ Reply to Thread
Results 1 to 6 of 6

User Defined Dynamic Cascading Listboxes

  1. #1
    Registered User
    Join Date
    04-29-2017
    Location
    London
    MS-Off Ver
    2016 Professional
    Posts
    50

    User Defined Dynamic Cascading Listboxes

    Hello All

    Sorry for the long post but I think I need to offer an explanation of what I am looking to achieve and how my current solution works. My objective is to provide the user with a 'template' where they can decide which features appear in each listbox, which lists are shown in the next listbox as a result of the previous selection and which features should be excluded from appearing in any future listboxes based on the previously selected items. If possible I would like to achieve this without vba. I have attached a working example of the spreadsheet. Although it works I fear my solution is not elegant and I am looking for peoples ideas / suggestions. The final template will need to handle 100+ possible features and may have 30+ listboxes. Also, the current solution requires that the List Numbers are defined in numerical sequence. If I added feature13 and assigned it to list 2 it would not appear.


    U]Explanation of Spreadsheet[/U]

    (In the below explanations I will make reference to tables for convenience although I appreciate they are not defined as Excel tables)

    Table1 : First table is the main control for the user allowing them to define which 'features' are grouped together to appear in a listbox. This is acheievd by entering a List Number against each feature. In the example below / attached features 1 and 2 will appear in list number 1, features 3,4, and 5 are assigned to appear in list number 2 etc. The Next List column allows the user to define which list should be displayed in the next listbox based on the selected value. In the example if the user selects feature1 in the first listbox the next listbox should display those items defined as appearing in list 2. If the user was to select feature 2 in the first listbox then the next listbox should display the items defined as appearing in list 3.

    Finally, the right hand side matrix allows the user to enter exceptions to prevent features from appearing in lists based on previous selections. This is achieved by entering an 'x' in the appropriate cell. In the example if feature 1 is selected then features 4 and 11 will be excluded from appearing in any future lists. If feature 2 was selected then feature 5 would be excluded.
    Table1.JPG

    Table 2 : The second table is just a helper to consolidate the selected features from the listboxes and their next list number.
    Attachment 567546

    Table 3 : This table is used to determine if features should be excluded from future lists based on previously selected features (false = excluded, true = include). If a feature has not been selected in one of the drop down lists then it returns true. If a feature has been selected and there is a corresponding 'x' then return false, otherwise return true. The included column uses the And function to determine if a feature should be included or not.
    Table3.JPG

    Table 4 : This table shows the features to include in the lists based on the logic value and whether the feature appears in the next list.

    Table 5 : This simply removes the blanks from table 4 using a standard approach of an array function. The listboxes use dynamic named ranges based in these consolidated lists to populate the listboxes using data validation
    Table4&5.JPG


    Going forwards the listboxes will appear on a userform and I will use vba to control the refresh of the listboxes etc.
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by rabbit_post; 03-27-2018 at 12:50 AM. Reason: Attaching document

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: User Defined Dynamic Cascading Listboxes

    With 30 listboxes but (for example) only 10 required, what happens with the 20 "empty" listboxes? How is this going to work from a user perspective? How will they know which list boxes are "Active"?

  3. #3
    Registered User
    Join Date
    04-29-2017
    Location
    London
    MS-Off Ver
    2016 Professional
    Posts
    50

    Re: User Defined Dynamic Cascading Listboxes

    In the final version the list boxes will be display on a userform. Here i will control the appearance of the boxes, eg to start with only one list box will be visible. Upon selection of a value the second listbox will become visible. This process will continue until the 'end' of the cascade is reached. By this way the user will not be confronted with a series on unused listboxes. I will also control the refresh of the lists and manage when the selection is changed in a previous listbox etc.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: User Defined Dynamic Cascading Listboxes

    Thank you for your reply: does this still require the generation of 30 listbox tables OR only those required i.e. based on the selection the list box "titles" in VBA are dynamic.

    Not doing the VBA but trying to ascertain whether a dynamic array of listboxes is required.

  5. #5
    Registered User
    Join Date
    04-29-2017
    Location
    London
    MS-Off Ver
    2016 Professional
    Posts
    50

    Re: User Defined Dynamic Cascading Listboxes

    On the userform I was considering only generating the listboxes required in accordance with the user specified number of lists, so keeping it dynamic.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: User Defined Dynamic Cascading Listboxes

    Also, the current solution requires that the List Numbers are defined in numerical sequence. If I added feature13 and assigned it to list 2 it would not appear.
    This could be addressed by allowing "free form" input and using VBA to sort to give you the table in F:H

    I doubt if there is any easy solution as it is a VERY complex matrix you are developing: a 100 X 100 matrix exclusion is not one I would manually want to have to complete.

    Obviously I don't know how users derive the Features/List/Next List table and how dynamic they are (frequency of change [nor how many variants there are]) but this is obviously the prime (potential) source of error. This the "key" to any solution.

+ 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. [SOLVED] Listboxes display headers and not populating defined range
    By terriertrip in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-06-2017, 09:59 PM
  2. VBA Cascading dropdown for user form
    By dbarcl10 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-15-2017, 07:27 AM
  3. Cascading combo box and dynamic ranges
    By penfold1992 in forum Excel General
    Replies: 2
    Last Post: 08-24-2013, 06:33 AM
  4. How to build a dynamic matrix table with user defined no.of rows and no.of columns
    By santhosh51 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 08-20-2013, 03:58 AM
  5. I need a help to make Vba Cascading Listboxes
    By cv24 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-02-2012, 06:41 AM
  6. VBA cascading listboxes.
    By harp in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 07-27-2010, 04:07 PM
  7. Cascading Dynamic Dropdown Lists
    By choop in forum Excel General
    Replies: 4
    Last Post: 06-14-2006, 12:30 PM

Tags for this Thread

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