Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 671
  • Last Modified:

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!
0
JohnDiddy77
Asked:
JohnDiddy77
3 Solutions
 
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
 
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
 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now