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!
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! 😞cking1333 The new error you're getting is most likely due to the RowSource property of the amendedcharge combo box. This property should NOT be set. If you've previously set it to reference a range in the workbook, remove the setting (do this with the parent combo box too). The code I've used will programmatically populate both combo boxes, so there is no need to set the RowSource property for either one.
RowSource Property
Hopefully that solves it! 😉
BOOM!
That's exactly what it was. I copied the Combo Box from another form and didn't even think of that. Nor would ever have thought of that.
Your code works totally awesome now!!! Thanks for your assistance and your patience!!!!
- Ok, I hadn't set up the table which I have now done and it is the only table on Sheet10. I have verified the sheet name in Step 2 and all is good. I tried adding the table name in Step 3 but now receive a different error as follows:
Run-time error '-2147467259 (80004005)': Unspecified error
The line of code that is now highlighted is this:
Me.amendedcharge.Clear
I have verified the spelling of the Combo Box with the code and it matches. I can also verify that it is the second box where a user would select the charge once they have specified the legislation in Combo box 1 (AKA "leg").
What I did notice however, is that box boxes contain the legislation type when the form is opened. If I select the legislation type from Combo box 1 and then try to select the charge from the second Combo box the error appears. This is the order it should be.
But...when I do it in reverse, Combo box 2 also shows the types of legislation but not the charges. If I select a legislation from there then goto Combo box 1, it too has the same list of legislation and I can select from the list. At that point, I could have TSSEA in Combo box 1 & 2 but no option to select charges. And no error is generated but I'm not getting the proper option selection.
Not sure why this is. Hope that helps with the error and where I can go from here.