How to Create Dependent Activex Dropdown Box for dynamic range

[Nitesh Khot]

Hi...

I want to know about how to create Dependent active-x dropdown box for dynamic range..

Thanks,
nitesh

 

[vletm]

@Nitesh Khot
Which do You mean ... a dropdown or a combobox?
(for me those are different)
... and why it have to be an ActiveX-component?

 

[Nitesh Khot]

Thanks for reply...

I am looking for Dependent Active-X combobox...for worksheet to change pivot table or filter worksheet using Active-x Combobox

 

[vletm]

... someone else should help with ActiveX-components.

 

[jindon]

1) To ThisWorkbook code module

Option Explicit
Private Sub Workbook_Open()
    If ActiveSheet Is Sheets("sheet1") Then
        Run Sheets("sheet1").CodeName & ".worksheet_activate"
    End If
End Sub

2) To Sheet1 code module

Option Explicit
Private dic As Object

Private Sub Worksheet_Activate()
    Dim a, i As Long
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    a = [d1].CurrentRegion.Value
    For i = 2 To UBound(a, 1)
        a(i, 1) = CStr(a(i, 1)): a(i, 2) = CStr(a(i, 2))
        If Not dic.exists(a(i, 1)) Then
            Set dic(a(i, 1)) = CreateObject("Scripting.Dictionary")
            dic(a(i, 1)).CompareMode = 1
        End If
        dic(a(i, 1))(a(i, 2)) = Empty
    Next
    Me.ComboBox1.List = dic.keys
End Sub

Private Sub ComboBox1_Change()
    Me.ComboBox2.Clear
    If Me.ComboBox1.ListIndex > -1 Then Me.ComboBox2.List = dic(Me.ComboBox1.Value).keys
End Sub