+ Reply to Thread
Results 1 to 12 of 12

Dependent Combo Boxes

  1. 07-06-2011, 03:56 PM #1
    HangMan
    HangMan is offline
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Dependent Combo Boxes

    I have been struggling to figure out how to create 'Dependent Combo Boxe'. I know how to do this using Data Validation, but I can't work it out for 'Form Combo Boxes'.

    I have my named ranges already set up and I have looked at various suggested VBA code, however, nothing I have tried works correctly.

    My lists are fixed, so there is no need to have the flexibility to expand any of the lists. I simply want to have two form Combo Boxes, where the drop down list that appears in the second Combo Box, is governed by the option chosen by the user from the first Combo Box.

    Is anyone able to help me with the relevant VBA code to allow this to work. I am a VBA novice, I can find my way around but am not very knowledgeable with VBA much beyond that, so any help would be greatly appreciated or if anyone has a simple working example, that I could look at, I would be very grateful.
    Last edited by HangMan; 07-08-2011 at 04:48 PM.
    Reply With QuoteRegister To Reply
  2. 07-06-2011, 05:07 PM #2
    barryleajo
    barryleajo is offline
    Valued Forum Contributor
    Join Date
    06-19-2010
    Location
    Holywell, N Wales, UK
    MS-Off Ver
    Excel 2013
    Posts
    470

    Re: Dependent Combo Boxes

    Hi HangMan
    You can accomplish cascading comboboxes without VBA
    I have mocked-up an example in the attached
    I do appreciate that you specified "form Combo boxes" so this may not fit your needs.
    If so ... well nothing lost eh!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 07-06-2011, 06:36 PM #3
    HangMan
    HangMan is offline
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Dependent Combo Boxes

    Barryleajo

    Wow, absolutely perfect, I've spent ages trying to figure this out using VBA. Very glad it can be done without. This works perfectly and using the right combo boxes, so thank you so much for your help...
    Reply With QuoteRegister To Reply
  4. 07-07-2011, 01:29 AM #4
    royUK
    royUK is offline
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Dependent Combo Boxes

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example
    Reply With QuoteRegister To Reply
  5. 07-07-2011, 02:25 AM #5
    barryleajo
    barryleajo is offline
    Valued Forum Contributor
    Join Date
    06-19-2010
    Location
    Holywell, N Wales, UK
    MS-Off Ver
    Excel 2013
    Posts
    470

    Re: Dependent Combo Boxes

    HangMan
    Its a pleasure and glad its helped you
    Thanks for the response and acknowledgement
    Reply With QuoteRegister To Reply
  6. 07-07-2011, 08:29 AM #6
    HangMan
    HangMan is offline
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Dependent Combo Boxes

    Hi Barryleajo

    I want to take this concept one stage further. I want to be able to choose an option based on the overall position in the combined list (i.e. List1 to List4), using a VLOOKUP, but to allow me to do this, would require changing the Input Range from 'rngChoose' in ComboBox2 to something like B10:B32 which means the second ComboBox won't function correctly.

    So I want to maintain the functionality of both Combo Boxes, but I want to be able to use a VLOOKUP from the combined 4 ranges to get another value and display this value elsewhere on the sheet - I don't know if I'm making any sense at all. I've updated your sheet to try an explain this better. The column on the right with the single Combo Box, shows what I want to achieve, but you will see the Input Range isn't rngChoose any longer.

    I can do the VLOOKUP bits, it is just how to configure the Combo Boxes to allow me to get the correct row number based on the 4 different ranges.

    Is there any way I can combine these two techniques to achieve this that you can think off?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 07-07-2011, 05:17 PM #7
    barryleajo
    barryleajo is offline
    Valued Forum Contributor
    Join Date
    06-19-2010
    Location
    Holywell, N Wales, UK
    MS-Off Ver
    Excel 2013
    Posts
    470

    Re: Dependent Combo Boxes

    HangMan
    If I have understood correctly see the example in the "Mk2" Tab in the attached.
    Note that this builds on the first example but I have renamed the lists etc.
    The value you require is in cell D16
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 07-08-2011, 04:30 PM #8
    HangMan
    HangMan is offline
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Dependent Combo Boxes

    barryleajo

    Thanks so much, this worked perfectly... Your help is very much appreciated, thank you for taking the time to find a solution...
    Reply With QuoteRegister To Reply
  9. 07-08-2011, 04:45 PM #9
    barryleajo
    barryleajo is offline
    Valued Forum Contributor
    Join Date
    06-19-2010
    Location
    Holywell, N Wales, UK
    MS-Off Ver
    Excel 2013
    Posts
    470

    Re: Dependent Combo Boxes

    Pleasure mate, glad you're sorted.
    As royUK has requested above, please mark as Solved if you are satisfied.
    Reply With QuoteRegister To Reply
  10. 07-08-2011, 04:48 PM #10
    HangMan
    HangMan is offline
    Forum Contributor
    Join Date
    06-21-2010
    Location
    -
    MS-Off Ver
    Excel 2010
    Posts
    1,211

    Re: Dependent Combo Boxes

    Have done and thanks once again...
    Reply With QuoteRegister To Reply
  11. 09-06-2011, 08:08 AM #11
    Ansar Hussain
    Ansar Hussain is offline
    Registered User
    Join Date
    09-06-2011
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Dependent Combo Boxes

    Can anyone help me doing the same with VBA .
    Reply With QuoteRegister To Reply
  12. 09-06-2011, 08:10 AM #12
    romperstomper
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,968

    Re: Dependent Combo Boxes

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.
    Everyone who confuses correlation and causation ends up dead.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1