How Do You Create Comboboxes With Multi-Directional Dependencies?

Hi Experts,

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

Controls

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

Question

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?

Thanks!
JohnDiddy77Asked:
Who is Participating?
 
Russell LucasIT Infrastructure Project ManagerCommented:
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.
0
 
Arno KosterCommented:
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.
0
 
JohnDiddy77Author Commented:
@ 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?
0
 
krishnakrkcCommented:
Hi,

You may find the following link useful.

http://www.excelfox.com/forum/showthread.php?31-Dependent-List 

Kris
0
 
Arno KosterCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.