Posts by Nikhil_Narwade

    Hi,

    I am using 3 combobox and each of the CB are dependent on each other. So, if I select data in any one CB then the the data in other two CB should get updated.

    In the below code when I select CB1 then CB2 is getting updated. But going ahead when I select a value in CB2 then CB3 is not getting updated.

    CB3 should get updated on the basis of data selected in CB1 and CB2.

    Attaching the file and below is the code –

    ******************************************************

    Private Sub ComboBox1_Change()

    Dim rng As Range

    Dim r As Range

    Dim Dic As Object

    Dim sh As Worksheet

    Dim ws As Worksheet

    Dim i As Integer

    Dim cb As ComboBox

    Dim ar As Variant

    Set sh = Sheet2 ‘Calc Sheet

    Set ws = Sheet3 ‘List Sheet

    ar = Array(“All Sub Categories”, “All Grades”)

    Application.EnableEvents = False

    Set rng = ws.Range(“A2”, ws.Range(“A” & Rows.Count).End(xlUp))

    Set Dic = CreateObject(“scripting.dictionary”)

    Dic.CompareMode = vbTextCompare

    Set sh = Sheet2

    For Each r In rng

    If r = ComboBox1 Then

    Dic(r.Offset(, 1).Value) = Empty

    End If

    Next

    With ComboBox2 ‘Add data to the comboboxes

    .List = Application.Transpose(Dic.keys)

    .AddItem “All Categories”, 0

    .ListIndex = 0

    End With

    ‘Add to cb 3 & 4

    For i = 3 To 4

    Dic.RemoveAll

    For Each r In rng

    If r = ComboBox1 Then

    Dic(r.Offset(, i – 1).Value) = Empty

    End If

    Next

    Set cb = Sheet1.Shapes(“ComboBox” & i).OLEFormat.Object.Object

    With cb ‘Add data to the comboboxes

    .List = Application.Transpose(Dic.keys)

    .AddItem ar(i – 2), 0

    .ListIndex = 0

    End With

    Next i

    For i = 1 To 4 ‘Loop through the comboboxes

    Set cb = Sheet1.Shapes(“ComboBox” & i).OLEFormat.Object.Object

    ‘sh.Activate

    ‘sh.Cells(2, i + 1).Select

    sh.Cells(2, i + 1) = cb.Value

    Next i

    Application.EnableEvents = True

    End Sub


    Private Sub ComboBox2_Change()

    Dim rng As Range

    Dim r As Range

    Dim Dic As Object

    Dim i As Integer

    Dim cb As ComboBox

    Dim sh As Worksheet

    Dim ws As Worksheet

    Set sh = Sheet2 ‘Calc Sheet

    Set ws = Sheet3 ‘List Sheet

    Application.EnableEvents = False

    Set rng = ws.Range(“B2”, ws.Range(“B” & Rows.Count).End(xlUp))

    Set Dic = CreateObject(“scripting.dictionary”)

    Dic.CompareMode = vbTextCompare

    If ComboBox2 = “All Categories” Then

    For Each r In rng

    Dic(r.Offset(, 1).Value) = Empty

    Next

    Else

    ‘Only items that relate to Combo 2

    For Each r In rng

    If r = ComboBox2 Then

    Dic(r.Offset(, 1).Value) = Empty

    End If

    Next

    End If

    With ComboBox3 ‘Add data to the comboboxes

    .List = Application.Transpose(Dic.keys)

    .AddItem “All Sub Categories”, 0

    .ListIndex = 0

    End With

    Dic.RemoveAll

    ‘Only items that relate to Combo 2

    For Each r In rng

    If r = ComboBox2 Then

    Dic(r.Offset(, 2).Value) = Empty

    End If

    Next

    With ComboBox4 ‘Add data to the comboboxes

    .List = Application.Transpose(Dic.keys)

    .AddItem “All Grades”, 0

    .ListIndex = 0

    End With

    sh.[c2] = ComboBox2.Value

    Application.EnableEvents = True

    End Sub


    Private Sub ComboBox3_Change()

    Dim rng As Range

    Dim r As Range

    Dim Dic As Object

    Dim sh As Worksheet

    Dim ws As Worksheet

    Set sh = Sheet2 ‘Calc Sheet

    Set ws = Sheet3 ‘List Sheet

    Application.EnableEvents = False

    Set rng = ws.Range(“C2”, ws.Range(“C” & Rows.Count).End(xlUp))

    Set Dic = CreateObject(“scripting.dictionary”)

    Dic.CompareMode = vbTextCompare

    If ComboBox3 = “All Sub Categories” Then

    For Each r In rng

    Dic(r.Offset(, 1).Value) = Empty

    Next

    Else

    ‘Only items that relate to Combo 3

    For Each r In rng

    If r = ComboBox3 Then

    Dic(r.Offset(, 1).Value) = Empty

    End If

    Next

    End If

    With ComboBox4

    .List = Application.Transpose(Dic.keys)

    .AddItem “All Grades”, 0

    .ListIndex = 0

    End With

    sh.[D2] = ComboBox3.Value

    Application.EnableEvents = True

    End Sub


    Private Sub ComboBox4_Change()

    Dim sh As Worksheet

    Set sh = Sheet2 ‘Calc Sheet

    Application.EnableEvents = False

    sh.[E2] = ComboBox4.Value

    Application.EnableEvents = True

    End Sub

    *********************************************************************