Repeating dependent ComboBoxes in a Userform

You are going to need to code each combobox pair in a similar fashion as what you submitted.

You should be able to just copy and paste the code from the ComboBox1_Change event to the CombboBox3_Change event and the ComboBox5_Change event. You will then need to change the associated combobox that affects it (i.e. ComboBox2 to 4, etc).

You will also want to amend the UserForm_Initialize event so that it properly populated your combobox1, 3, and 5 (see below code)

Please Login or Register  to view this content.

Example:

Please Login or Register  to view this content.

Over fifty pairs, that calls for a Class Module
Add a Class Module. Use the Properties Window to change its name to clsPairedDependents. Then put this code into the class's code module.

Please Login or Register  to view this content.

In this Demo user form, I used only 6 Comboboxes and made ComboBox1 the Master box for Combobox2, Combobox3 masters ComboBox4, etc.
The loop in the Initialize event will have to be modified to match the pairings that you want.
Notice that changing either of the paired boxes will 1) make that pair the ActiveDependentPair and fire the ActiveDependentPair_Change event.
In this demo form, the ActiveDependentPair_Change event sets the .Caption of a Label to show some of the properties of clsDependentPair. (The Object Browser will show all of those properties, they mirror the properties of a ComboBox)

Depending on if the Master or the Dependent of the pair caused the Change, either the ActiveDependentPair_MasterChange ActiveDependentPair_DependentChange will fire after the _Change event (but never both)

Please Login or Register  to view this content.

The Creeping Feature Creature overcame me on that version. It got a bloated and cumbersome. This is leaner.

The custom Class clsDependentPairs has properties:

MasterBox
'-- the control that determines which DependentList is shown in the DepenedentBox.
'-- MasterBox can be either a ComboBox or a ListBox

DependentBox
'-- the control that shows the DependentList, depending on which item is selected from the MasterBox.
'-- DependentBox can be either a ComboBox or a ListBox

MasterList is a pass-through property that acts like .MasterBox.List
DependentList(index) is what is shown when MasterBox.ListIndex = Index.
'-- Typicaly DependentList(index) is a one dimensional array.
'-- If DependentList(index) = Null, then the DependentBox will be cleared when MasterBox.ListIndex = Index
'-- If DependentList(index) = "nc" or "No Change" (or any string), setting MasterBox.ListIndex to index will not change the list in DependentBox.
'-- index can be -1. DependentBox will show DependentList(-1) when nothing is selected in the MasterBox.

Note that either the MasterBox or the DependentBox can be multi-column. They cannot be multi-select.

It will also pass a Change event to the (optional) ChangedPair variable in the Userform. The DependentChanged argument will be True/False depending on whether the ChangedPair.DependentBox or ChangedPair.Masterbox was changed.

In the attached, the Userform_Intialize event shows one way to match master controls with their dependent control.
The ChangedPair_Change event fills Label1 with information.

Please Login or Register  to view this content.