Forum Discussion
1 List with 2 dependent Combo boxes...is it possible?
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!
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 Sub
When 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.
So, I didn't realize I needed to set up a table which I have now done. I verified the sheet name per Step 2 of your reply and tried Step 3 but that didn't change any results.
I am now getting a different error:
Run-time error: '-2147467259 (80004005)': Unspecified error.The section that is highlighted when I select debug is this:
Me.amendedcharge.Clear
When I load the form, the legislation type show up in Combo box 1 (AKA leg) and I can select an option however when I go to select a charge from Combo box 2 (AKA amendedcharge) I get the error.
Also, when I load the form, Combo box 2 shows the legislation types and so does Combo box 1. I have verified the name of box Combo boxes against the code and they match so I'm not sure what I'm doing wrong here.
We got rid of one error which is good but now there's another! 😞
