Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2006
    Location
    Tucson, Arizona
    Posts
    19

    Unanswered: Cascading Comboboxes in Excel?

    I'm yanking my hair out over this one.

    I've got a general idea of how to build cascading comboboxes in Excel using lookups and VBA, but it's the end of the day and I'm brain-dead.

    Can someone please post step-by-step instructions? You don't have to go into great detail; I've got the gist of it, but I'm goofing something up and I keep getting errors. Row1 contains headers, so the selection range starts with Row2.

    What I've got is:
    A1 Department
    A2 Department1
    A3 Department2
    A4 Department 3
    A5 Department 4
    B1 Subdepartment1
    B2 SubdeptA
    B3 SubdeptB
    B4 SubdeptC
    B5 SubdeptD
    C1 Subdepartment2
    C2 SubdeptE
    C3 SubdeptF
    C4 SubdeptG
    C5 SubdeptH
    D1 Subdepartment3
    D2 SubdeptI
    D3 SubdeptJ
    D4 SubdeptK
    D5 SubdeptL
    E1 Subdepartment4
    E2 SubdeptM
    E3 SubdeptN
    E4 SubdeptO
    E5 SubdeptP

    When a Department is selected, I'd like the appropriate combobox list to appear so the user can select a Subdepartment from that list. For example, when the user selects Department 1 from a combobox, then the second combobox should contain Subdepartment1 selections (SubdeptA, SubdeptB, SubdeptC, and SubdeptD). Likewise, when Department 2 is selected, a combobox containing Subdepartment2 selections should drop down -- and so forth.

    I think the problem might be in defining the range, or assigning a rowsource. Any help would be greatly appreciated!

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. Dependent Lists is the way to go (without VBA). See Dependent Lists for a solution.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2008
    Posts
    19
    That method is new to me, I wanted to go all VBA (but that's just me)

    Maybe a tip (Shades, correct me if I am wrong): see if you can use lists to create the columns (each column a seperate list): why?

    Lists are a sort of named ranges which you can add and delete items from. If doing so, your list's range will automatically scale with it. That means that you do not need to update the ranges when you add/remove a row of data.

    Hope it helped ya out a bit

  4. #4
    Join Date
    Jul 2006
    Location
    Tucson, Arizona
    Posts
    19
    Thank you! I KNEW I was doing it the hard way, but I just couldn't figure out the easy way. You guys ROCK.

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Just remember that if you are changing the number of items in each list, then be sure to make the named ranges dynamic, i.e. like this.

    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1:
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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