Combination of 2 combo boxes to populate 3rd

Thank you for helping. I am used to working with Access, but am trying to
work on a project where I'm required to use Excel programming. If you would
make some notes in your programming, that would be very helpful for me to
learn.

This is where I need some help, and I'll draw it out since I think it will
explain it better:

Level Age 8 Age 9 Age 10
Level 1 Coach A&B Coach B&C Coach D&E
Level 2 Coach F&G Coach H&I Coach J&K

I would like to make 3 combo boxes, the first of which would ask for a
child's level (cboLevel), the second would prompt for age (cboAge), and the
third combo box would display a choice of coach (cboCoach) based on the
answers from the prior two combo boxes.

Thanks in advance for your help.

This can be done: you need to set up data ranges somewhere (maybe on a hidden
sheet) with each set of selections listed together, e.g.

Coach A Coach D
Coach B Coach E ...

Coach B ....
Coach C

The code below should work, substituting in the proper ranges; I am sure it
could be simplified, too, if you lay out your coaches lists smartly and use
some math to match the range to the selections in the first 2 comboboxes...

Private Sub cboAge_Change()

Select Case Range(LinkedCellforAge)
Case 8
If Range(LinkedCellforLevel) = "Level 1" Then cboCoach.ListFillRange =
Range(CoachesRange1)
If Range(LinkedCellforLevel) = "Level 2" Then cboCoach.ListFillRange =
Range(CoachesRange2)
Case 9
If Range(LinkedCellforLevel) = "Level 1" Then cboCoach.ListFillRange =
Range(CoachesRange3)
If Range(LinkedCellforLevel) = "Level 2" Then cboCoach.ListFillRange =
Range(CoachesRange4)
Case 10
If Range(LinkedCellforLevel) = "Level 1" Then cboCoach.ListFillRange =
Range(CoachesRange5)
If Range(LinkedCellforLevel) = "Level 2" Then cboCoach.ListFillRange =
Range(CoachesRange5)
End Select

End Sub

Private Sub cboLevel_Change()

Call cboAge_Change

End Sub



"AJ" wrote:

> Thank you for helping. I am used to working with Access, but am trying to
> work on a project where I'm required to use Excel programming. If you would
> make some notes in your programming, that would be very helpful for me to
> learn.
>
> This is where I need some help, and I'll draw it out since I think it will
> explain it better:
>
> Level Age 8 Age 9 Age 10
> Level 1 Coach A&B Coach B&C Coach D&E
> Level 2 Coach F&G Coach H&I Coach J&K
>
> I would like to make 3 combo boxes, the first of which would ask for a
> child's level (cboLevel), the second would prompt for age (cboAge), and the
> third combo box would display a choice of coach (cboCoach) based on the
> answers from the prior two combo boxes.
>
> Thanks in advance for your help.

"K Dales" wrote:

> This can be done: you need to set up data ranges <snip>
>
> The code below should work, substituting in the proper ranges...

A few additional points--This assumes we're on a worksheet using the ActiveX
ComboBoxes (the ones in the "Control Toolbox" toolbar, not the one on the
Forms toolbar.) If these ComboBoxes are on a form, the property you'd use to
populate them would be RowSource instead of ListFillRange.

And given the 'business constraints' of the application, any time you change
one of the first two comboBoxes, the entry in the third will likely be
invalid. So you should probably clear the cboCoach comboBox any time the
cboAge or cboLevel comboBox changes. But don't use the Clear method--that
will result in a run-time error with a helpful explanation like: "Run-time
error. -2310293840921834123." What that's trying to say is that you can't
Clear a ComboBox that has something in the ListFillRange/RowSource property.
You have to do something like this (clearing cboCoach when cboLevel changes.
Do something similar for cboAge.)

Private Sub cboLevel_Change()
cboCoach.ListFillRange = ""
cboCoach.Value = ""
End Sub