none
Multiple Dependency combo box Excel 2010 VBA. RRS feed

  • Question

  • Hi,

    I am trying to populate values from the database sheet. I have already build combo box using vba. attached vba code below for reference.

    The problem is am getting only the first cell values of  column item1. i need to get all the items selection branch,AGM,RSM,ALE,BRAND

    example: selection from combo box
                   branch > Branch1
                   AGM > AGM1
                   RSM > RSM1
                   ALE > ALE1
                   BRAND > BRAND1

    OUTPUT>>>>>  

    Item ITEM1 ITEM2 ITEM3
    Total Qty 2 1 1
    Qty-% 50% 25% 25%
    Stock Norm 24 24 24
    Closing Stock 10 10 13
    Stock Allocation 12 6 6
    Stock Status -2 4 7
    Excess/Less Less Excess Excess


    DATABASE

    COMBOX VB CODE:

    Private Sub choice1_Change()
        Range("F8:F15").ClearContents
        Choice2.ListIndex = -1
        Choice3.ListIndex = -1
        Choice4.ListIndex = -1
        Choice5.ListIndex = -1
        If choice1.ListIndex > -1 Then Choice2.List = Split(f_list(1), ",")
    End Sub
    Private Sub choice2_Change()
        If Choice2.ListIndex > -1 Then Choice3.List = Split(f_list(2), ",")
    End Sub
    Private Sub choice3_Change()
        If Choice3.ListIndex > -1 Then Choice4.List = Split(f_list(3), ",")
    End Sub
    Private Sub choice4_Change()
        If Choice4.ListIndex > -1 Then Choice5.List = Split(f_list(4), ",")
    End Sub
    Function f_list(x)
        sn = Sheets("database").Cells(1).CurrentRegion
        For j = 1 To UBound(sn)
            For jj = 1 To x
                   If sn(j, jj) <> Sheets("Stock").OLEObjects("choice" & jj).Object.Value Then Exit For
            Next
            If jj = x + 1 And InStr(c01 & ",", "," & sn(j, jj) & ",") = 0 Then c01 = c01 & "," & sn(j, jj)
        Next
        f_list = Mid(c01, 2)
    End Function
    
    
    Private Sub choice5_Change()
        If Choice5.ListIndex = -1 Then Exit Sub
        
        sn = Sheets("database").Cells(1).CurrentRegion
        c01 = choice1.Value & Choice2.Value & Choice3.Value & Choice4.Value & Choice5.Value
    
        For j = 1 To UBound(sn)
            If sn(j, 1) & sn(j, 2) & sn(j, 3) & sn(j, 4) & sn(j, 5) = c01 Then
                Range("F8:F15") = Application.Transpose(Array(sn(j, 6), sn(j, 7), sn(j, 8), sn(j, 9), sn(j, 10), sn(j, 11), sn(j, 12), sn(j, 13)))
                Exit For
            End If
        Next
    End Sub


    Private Sub Workbook_Open()
        sn = Sheets("database").Cells(1).CurrentRegion
        For j = 1 To UBound(sn)
            If InStr(c01 & ",", "," & sn(j, 1) & ",") = 0 Then c01 = c01 & "," & sn(j, 1)
        Next
        
        With Sheets("Stock")
            .choice1.List = Split(Mid(c01, 2), ",")
            .Choice2.Clear
            .Choice3.Clear
            .Choice4.Clear
            .Choice5.Clear
            .Range("F8:F15").ClearContents
        End With
    End Sub
    



    GSKR

    Sample table;

    Branch AGM RSM ALE Brand Item Total Qty Qty-% Stock Norm Closing Stock Stock Allocation Stock Status Excess/Less
    Branch1 AGM1 RSM1 ALE1 BRAND1 ITEM1 2 50% 24 10 12 -2 Less
    Branch2 AGM2 RSM2 ALE2 BRAND2 ITEM2 1 25% 24 10 6 4 Excess
    Branch3 AGM3 RSM3 ALE3 BRAND3 ITEM3 1 25% 24 13 6 7 Excess
    Branch4 AGM4 RSM4 ALE4 BRAND4 ITEM4 1 100% 0 10 0 10 Excess
    Branch5 AGM5 RSM5 ALE5 BRAND5 ITEM5 3 100% 7 18 7 11 Excess
    Branch6 AGM6 RSM6 ALE6 BRAND6 ITEM6 3 100% 22 20 22 -2 Less
    Branch7 AGM7 RSM7 ALE7 BRAND7 ITEM7 3 33% 103 19 34 -15 Less
    Branch8 AGM8 RSM8 ALE8 BRAND8 ITEM8 2 22% 103 19 23 -4 Less
    Branch9 AGM9 RSM9 ALE9 BRAND9 ITEM9 1 11% 103 14 11 3 Excess
    Branch10 AGM10 RSM10 ALE10 BRAND10 ITEM10 1 11% 103 12 11 1 Excess
    Branch11 AGM11 RSM11 ALE11 BRAND11 ITEM11 2 22% 103 12 23 -11 Less
    Branch12 AGM12 RSM12 ALE12 BRAND12 ITEM12 2 25% 34 11 9 3 Excess
    Branch13 AGM13 RSM13 ALE13 BRAND13 ITEM13 1 13% 34 18 4 14 Excess
    Branch14 AGM14 RSM14 ALE14 BRAND14 ITEM14 4 50% 34 18 17 1 Excess
    Branch15 AGM15 RSM15 ALE15 BRAND15 ITEM15 1 13% 34 18 4 14 Excess
    Branch16 AGM16 RSM16 ALE16 BRAND16 ITEM16 1 7% 141 15 10 5 Excess


    • Edited by GSKR Tuesday, October 16, 2018 4:33 AM
    Friday, September 21, 2018 8:36 AM

All replies

  • Hi GSKR,

    I'm still a little confused about this Combo operation. Is this data loaded  from the database sheet? Or just your filter?

    Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Monday, September 24, 2018 10:14 AM
    Moderator
  • Its loaded from database sheet.

    GSKR

    Monday, September 24, 2018 10:51 AM
  • Hi GSKR,

    Unfortunately, we can't reproduce your problem.

    If possible, could you please share your Excel?

    Best Regards,

    Yuki



    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Tuesday, September 25, 2018 7:37 AM
    Moderator