Forum Discussion
1 List with 2 dependent Combo boxes...is it possible?
Hi all,
Not sure if this ask is even possible but here goes...
I have a 2 column list. Column 1 contains different type of legislation (i.e. HTA, CAIA, TSSEA, etc.). Column 2 has charge descriptions available for each type of legislation. Can I have a userform with 2 Combo boxes with the first listing the different types of legislation and the 2nd being dependent on the first for charge options.
So, if HTA is selected from Combo box 1, Combo box 2 should only show the charge options from the HTA. This would sort of be like the indirect function within a spreadsheet but instead of having a new list for each type of legislation, there would only be 1 list that contains all.
Can this be done and if so how?
Thanks in advance!!!
cking1333 There's a few ways this can be accomplished. In the following example, I've used a structured Excel table on Sheet1 to hold the 2-column list, and I've named the two combo boxes cboType (parent) and cboOptions (dependent)...
UserForm with Dependent ComboBox
The code used in the module for UserForm1 was:
Option Explicit Private Sub cmdCancel_Click() Unload Me End Sub Private Sub cboType_AfterUpdate() Dim arr As Variant, i As Long, str As String arr = Sheet1.ListObjects(1).DataBodyRange.Value str = Me.cboType.Value Me.cboOptions.Clear For i = LBound(arr, 1) To UBound(arr, 1) If arr(i, 1) = str Then Me.cboOptions.AddItem arr(i, 2) End If Next i End Sub Private Sub UserForm_Initialize() Dim dict As Object, arr As Variant, i As Long, str As String Set dict = CreateObject("Scripting.Dictionary") arr = Sheet1.ListObjects(1).DataBodyRange.Value For i = LBound(arr, 1) To UBound(arr, 1) str = arr(i, 1) If Not dict.Exists(str) Then dict.Add str, str Me.cboType.AddItem str End If Next i Set dict = Nothing End SubWhen the user form is first initialized, the parent combo box is populated with the unique items from column 1. Each time the parent combo box is updated, the dependent combo box is cleared and re-populated with the matching options. Cheers!
7 Replies
cking1333 There's a few ways this can be accomplished. In the following example, I've used a structured Excel table on Sheet1 to hold the 2-column list, and I've named the two combo boxes cboType (parent) and cboOptions (dependent)...
UserForm with Dependent ComboBox
The code used in the module for UserForm1 was:
Option Explicit Private Sub cmdCancel_Click() Unload Me End Sub Private Sub cboType_AfterUpdate() Dim arr As Variant, i As Long, str As String arr = Sheet1.ListObjects(1).DataBodyRange.Value str = Me.cboType.Value Me.cboOptions.Clear For i = LBound(arr, 1) To UBound(arr, 1) If arr(i, 1) = str Then Me.cboOptions.AddItem arr(i, 2) End If Next i End Sub Private Sub UserForm_Initialize() Dim dict As Object, arr As Variant, i As Long, str As String Set dict = CreateObject("Scripting.Dictionary") arr = Sheet1.ListObjects(1).DataBodyRange.Value For i = LBound(arr, 1) To UBound(arr, 1) str = arr(i, 1) If Not dict.Exists(str) Then dict.Add str, str Me.cboType.AddItem str End If Next i Set dict = Nothing End SubWhen the user form is first initialized, the parent combo box is populated with the unique items from column 1. Each time the parent combo box is updated, the dependent combo box is cleared and re-populated with the matching options. Cheers!
So I've added your code in, with amendments to match my names, but I am getting an error as follows: Run-time error '9': Subscript out of range
Sheet10 (AKA Legislation) has only 2 columns and here is my code now:
Option Explicit Private Sub cmdCancel_Click() Unload Me End Sub Private Sub leg_AfterUpdate() Dim arr As Variant, i As Long, str As String arr = Sheet10.ListObjects(1).DataBodyRange.Value str = Me.leg.Value Me.amendedcharge.Clear For i = LBound(arr, 1) To UBound(arr, 1) If arr(i, 1) = str Then Me.amendedcharge.AddItem arr(i, 2) End If Next i End Sub Private Sub ResolveCourtMatter_Initialize() Dim dict As Object, arr As Variant, i As Long, str As String Set dict = CreateObject("Scripting.Dictionary") arr = Sheet10.ListObjects(1).DataBodyRange.Value For i = LBound(arr, 1) To UBound(arr, 1) str = arr(i, 1) If Not dict.Exists(str) Then dict.Add str, str Me.leg.AddItem str End If Next i Set dict = Nothing End SubNot sure what I am doing wrong because adding my names into your code seems straight forward. This is the line that is highlighted yellow when the error appears:
arr = Sheet10.ListObjects(1).DataBodyRange.Value
The first Combo Box shows the types of legislation, but the error occurs when I try to select the 2nd Combo Box that should be populated with the charges.
Thanks.
cking1333 Only 3 things come to mind that could be causing the "Subscript out of range" error, and all 3 are related to the definition of the arr variable.
(1) Did you format your list data as an Excel table (ListObject)? If not, you can do so by selecting a cell within the list range, then going to Insert > Table on the ribbon (Ctrl+T). Be sure to check "My table has headers", if applicable.
Insert > Table
(2) Is Sheet10 actually Sheet10? Worksheet objects have 2 different name properties: Name and CodeName. The Name property refers to the sheet "tab" shown in the Excel workbook, whereas the CodeName is shown in the Properties Window of the Visual Basic Editor. It's entirely possible for them to differ. When using Sheet10 in this manner, you're referring to the CodeName.
Sheet Name vs CodeName Property
(3) Is there more than one Excel Table (ListObject) on Sheet10? If so, refer to the desired table by name, rather than index number, to make sure you're referencing the correct table (e.g. Sheet10.ListObjects("Table1")). If ListObject(1) only contains a single column, you'll get the "Subscript out of range" error when changing the parent combo box.
