+ Reply to Thread
Results 1 to 8 of 8

Cascading Comboboxes With Multiple Dynamic Named Ranges?

  1. #1
    Registered User
    Join Date
    03-15-2013
    Location
    LonDon
    MS-Off Ver
    Excel 2003/2007
    Posts
    3

    Cascading Comboboxes With Multiple Dynamic Named Ranges?

    Hello Everyone,

    I am developing a Userform but have come across an issue regarding cascading comboboxes and dynamic named ranges.

    I have a worksheet with columns of data. The column count can increase/decrease based on future requirements. The column headings have been placed into a dynamic named range (DnrSkills). Lets assume there are 3 columns set like this:
    A B C ---------(DnrSkills)
    1 Day Adv Night
    2 Depart Hover NRF
    3 Arrive Fly Hover

    When my Userform loads, it automatically creates Dynamic named ranges based on the columns in the worksheet. I now have 4 ranges, DnrSkills, Day, Adv and night

    Here is the issue that I would like help with:

    Combobox 1 is populated using the dynamic range DnrSkills. The dropdown list contains Day, Adv, Night. (this works fine)

    I need Combobox 2 to look at whatever value isselected in combobox 1 (lets assume Day). Recognise that 'Day' is the name of a Dynamic named range created when the userform opened, and therefore populate itself accordingly (Depart, Arrive).

    I have searched online for a few days now, and cannot find a solution to a problem like this.

    Is what I am trying to achieve possible? And if so, would you please help this novice out!

    Thanks in advance.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Cascading Comboboxes With Multiple Dynamic Named Ranges?

    You would handle this in the ComboBox?_Change event. Since you know how to populate the values of the ComboBox1 already, based on your question, your ComboBox1_Change event macro should simple test for each possible value, and populate ComboBox2 options accordingly:

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    03-15-2013
    Location
    LonDon
    MS-Off Ver
    Excel 2003/2007
    Posts
    3

    Re: Cascading Comboboxes With Multiple Dynamic Named Ranges?

    JBeaucaire,

    Thank you for your reply.

    I have tried your idea, but if possible, I need to avoid the use of 'hard coding' the Combo Box contents of Combo Box 2 by using (Case 1 "Day"), (Case 2 "Adv") etc.
    This is because number of columns that populate Combo Box 1 will change over time), and that someone else will be using this workbook who has even less of a clue about VBA than I do!

    The value of Combo Box 1 will always be the Name of a Dynamic Named Range within the worksheet (Created automatically when the Userform opens).

    Ideally, this is what I want to happen once Combo Box 1's value is Selected/Changed:

    1) Combo box 2 to look at the value of Combo Box 1 (This will always be the name of an existing Dynamic Named Range).

    2) Confirm the the Combo Box 1 value = an existing Dynamic Named Range (Which it should always do).

    3) Combo Box 2 then populates itself with the corresponding Dynamic Named Range.


    My apologies if I have missed the point in your reply, but I really am at the "day 1, week 1" stage of learning Excel Userforms and VBA.

    I have attached a copy of my workbook to assist.

    Skills.xls

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Cascading Comboboxes With Multiple Dynamic Named Ranges?

    This is really all you need based on that design.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-15-2013
    Location
    LonDon
    MS-Off Ver
    Excel 2003/2007
    Posts
    3

    Thumbs up Re: Cascading Comboboxes With Multiple Dynamic Named Ranges?

    Well that is tremendous - your code works perfectly, and now my cat is no longer at risk of finding itself in low earth orbit.

    Thank you for your time on this issue, it's really appreciated.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Cascading Comboboxes With Multiple Dynamic Named Ranges?

    I have marked this thread solved for you.
    In the future please select Thread Tools from the menu above and mark the thread as solved. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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