How Do You Create Comboboxes With Multi-Directional Dependencies?

Posted on 2011-05-11
Last Modified: 2012-05-11
Hi Experts,

I’m creating an Excel application for a client using VBA.


cboCity: City is dependent on Country.
cboSponsor: SPONSOR can be determined by Country/City.
cboAccountType: Account Type is dependent on Country, City and SPONSOR.


The client wants maximum flexibility in a UserForm that activates on WorkBook_Open to capture initial data to drive the worksheet. So, I must meet the following conditions:

Scenario A
User selects COUNTRY first.
•      Filter cboCITY based on country THEN
•      Filter cboSPONSORS to show relevant SPONSORS related to COUNTRY and CITY.

Scenario B
User Selects SPONSOR first, because they know the code.
•      Filter cboCOUNTRY and cboCITY based on SPONSOR

Scenario C
Scenario A and Scenario B will result in a list of ACCOUNT TYPES specific to Country, City and Sponsor.

What is the best way to proceed to create a UserForm with this sort of multi-directional dependencies in Excel 2003?

Question by:JohnDiddy77
    LVL 19

    Assisted Solution

    you can add a userform, populate it with the controls, and use vba macro's to fill the controls with data.

    for example use the cboCountry_Change event to read the selected country, determine the apropriate contents for city and sponsors, and update these controls.
    if you take care to choose the events such that they will not fire when you update the controls with new data this should not pose that much problems.

    Author Comment

    @ akoster: How about this: Create all of the dependencies with dynamic ranges within the worksheet itself, then use events to control what is shown in the form? The template will be used on older systems, so performance is key. Won't this proposed method slow things down?
    LVL 6

    Accepted Solution

    I would suggest, if your concerned about performance, that rather than using events to trigger the update put control buttons in.

    That way the update will only happen at the point where the user selects it to, which should reduce the workload. More importantly the perception of the user would be that the performance would be effected by a triggered event, This often helps with making users feel that things are working for them not against.
    LVL 18

    Expert Comment


    You may find the following link useful.

    LVL 19

    Assisted Solution

    Of course the performance of the solution depends greatly on the number of sponsors / cities / countries and account types, but especially for older systems the best approach would be to keep calculations to an absolute minimum.
    This more or less means that you only want to re-calculate which data you want the user to see and act on,  whenever something happens that forces you to perform this calculation.

    The most user-friendly way to do this is by using events, but as funinig stroll indicates this can also be done using a 'refresh' button or so.
    Although setting the dependancies in the worksheet itself is certainly possible, especially for large data sets the chosen implementation can easily brake the performance.

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now