Userform ComboBox based on another ComboBox

[tdahlman]

I have a userform with 2 Combo Boxes (cbCategory and cbSubCategory).

cbCategory is is populated based on the named range "categories" consisting of Income, Home, Daily Living, Transportation, Health & Recreation, Vacation, Dues & Subscriptions, Debts, Savings, and Misc.

Based on the selection in cbCategory I want cbSubCategory to populate with the appropriate named rage:
- Income = "Income"
- Home = "Home"
- Daily Living = "Daily"
- Transportation = "Transport"
- Health & Recreation = "Health"
- Vacation = "Vacation"
- Dues & Subscriptions = "Dues"
- Debts = "Debts"
- Savings = "Savings"
- Misc = "Misc"

I have my code figured out for the UserForm_Initialize() so that my userform is cleared and the cbCategory is populated with the named range "categories".
Now I just need to figure out my code for cbCategory_Change() to use the correct named range based on the cbCategory selection.

Any help or direction would be greatly appreciated.

Thanks,
Travis

 

[dmt32]

Hi,
you could try adding your range names to an array & then access the array elements to return range name by using the combobox listindex property. Something like this:

Dim myarray()
Private Sub cbCategory_Change()
    With Me.cbSubCategory
        .Clear
        .List = _
        Range(myarray(Me.cbCategory.ListIndex)).Value
        .ListIndex = 0
    End With
End Sub

Private Sub Userform_Initialize()
    Dim i As Integer
    Dim RangeName As Variant
    
    i = 0
    For Each RangeName In ThisWorkbook.Names
        'Named ranges
        If RangeName.Name <> "categories" Then
            ReDim Preserve myarray(i)
            myarray(i) = RangeName.Name
            i = i + 1
        End If
    Next RangeName
    
    Me.cbCategory.List = myarray
    
End Sub

Hope Helpful

Dave