Suggestions Dependent comboboxes

1 Dependent comboboxes

You can guide a user to make a selection from a database.
If the selection can be narrowed down making dependent choices you can use comboboxes or listboxes to guide the user.
The choices of each combobox can be defined beforehand, but you can also let VBA compose the next set of choices 'on the fly'
Comboboxes/Listboxes can appear in a userform or as ActiveX-control in a worksheet.
Because they need different VBA code I'll discuss them separately.
Most of the time you only want unique items in a combobox, so in the example code this will be provided too.
If you want the items in a combobox/listbox to be sorted it's rather easy to accomplish applying VBA, but for clarity's sake I will not provide that in the example code.

In VBA there's a multitude of methods to compose dependent lists of choices; the first code presented here makes use of 2 loops.
Method II produces the same result using more compact VBA code.

2.1 Comboboxes (ActiveX-controls) in a worksheet

Active-X controls
Description of the example file.
- Worksheet 'database' contains the data of the database.
- Worksheet 'output' contains three comboboxes 'choice1', 'choice2' and 'choice3'.
- The digit in the combobox's name matches a column in the database to keep the VBA-code as simple as possible.

When opening the workbook:
- read the 'database' into array sn
- combobox 'choice1' in worksheet 'output' gets a list of unique items in column 1 (A) of the database.
- comboboxes 'choice2' and 'choice3' are being emptied.
Private Sub Workbook_Open()
sn = Sheets("database").Cells(1).CurrentRegion
For j = 1 To UBound(sn)
If InStr(c01 & ",", "," & sn(j, 1) & ",") = 0 Then c01 = c01 & "," & sn(j, 1)
Next

With Sheets("output")
.choice1.List = Split(Mid(c01, 2), ",")
.choice2.Clear
.choice3.Clear
.Range("B5:B7").ClearContents
End With
End Sub
On changing a combobox:
Every time the value of a combobox changes, a new list of dependent options will be produced; the '_change'-event.
This code is part of the sheet's codemodule; the comboboxes can be referred to by 'choice1', 'choice2', 'choice3'.
Every combobox uses similar code to produce optionlists; therefore we use the function 'f_list'.
Each combobox's 'change'-event calls the function 'f_list'.

- check whether the changed combobox contains a value
- if so, filter in array sn the items that match the value in that combobox
- add the value in the next column to the string c01, if string c01 doesn't contain that value.
- convert the string to a 1-dimensional array to populate the next -dependent- combobox.
Private Sub choice1_Change()
Range("B5:B7").ClearContents
choice2.ListIndex = -1
choice3.ListIndex = -1

If choice1.ListIndex > -1 Then choice2.List = Split(f_list(1), ",")
End Sub
Private Sub choice2_Change()
If choice2.ListIndex > -1 Then choice3.List = Split(f_list(2), ",")
End Sub
Function f_list(x)
sn = Sheets("database").Cells(1).CurrentRegion

For j = 1 To UBound(sn)
For jj = 1 To x
If sn(j, jj) <> Sheets("output").OLEObjects("choice" & jj).Object.Value Then Exit For
Next

If jj = x + 1 And InStr(c01 & ",", "," & sn(j, jj) & ",") = 0 Then c01 = c01 & "," & sn(j, jj)
Next

f_list = Mid(c01, 2)
End Function

2.2 Comboboxes (ActiveX controls) in a worksheet: method II

Method II ActiveX-controls
The same result using more compact VBA code.

3.1 Comboboxes in a Userform

Userform controls
Description of the example file.
- The worksheet 'database' contains the data.
- The userform 'scherm' contains the comboboxes choice1, choice2 and choice3.
- The digit in the combobox's name matches a column in the database to keep the VBA-code as simple as possible.

Initialising the Userform:
- declare an array sn as a private variable
- read the 'database' and put it into array sn
- assign to combobox 'choice1' a list of unique items in column 1 of the database.
Dim sn

Private Sub UserForm_Initialize()
sn = Sheets("database").Cells(1).CurrentRegion
For j = 1 To UBound(sn)
If InStr(c01 & ",", "," & sn(j, 1) & ",") = 0 Then c01 = c01 & "," & sn(j, 1)
Next

choice1.List = Split(Mid(c01, 2), ",")
End Sub
On changing the value of a combobox:
Every time the value of a combobox changes, a new list of dependent options will be produced; the '_change'-event.
This code is part of the userform's codemodule; the comboboxes can be referred to by 'choice1', 'choice2', 'choice3'.
Every combobox uses similar code to produce optionlists; therefore we use the function 'f_list'.
Each combobox's 'change'-event calls the function 'f_list'.

- check whether the changed combobox contains a value
- if so, filter in array sn the items that match the value in that combobox
- add the value in the next column to the string c01, if string c01 doesn't contain that value.
- convert the string to a 1-dimensional array to populate the next -dependent- combobox.
Private Sub choice1_Change()
choice2.ListIndex = -1
choice3.ListIndex = -1
If choice1.ListIndex > -1 Then choice2.List = Split(f_list(1), ",")
End Sub
Private Sub choice2_Change()
If choice2.ListIndex > -1 Then choice3.List = Split(f_list(2), ",")
End Sub
Function f_list(x)
For j = 1 To UBound(sn)
For jj = 1 To x
If sn(j, jj) <> Me("choice" & jj).Value Then Exit For
Next
If jj = x + 1 And InStr(c01 & ",", "," & sn(j, jj) & ",") = 0 Then c01 = c01 & "," & sn(j, jj)
Next

f_list = Mid(c01, 2)
End Function

3.2 Comboboxes in a Userform: Method II

Method II UserForm controls
The same result using more compact VBA code.