none
Code to set a Combobox cascade RRS feed

  • Question

  • Hey Guys

    I´m working on a code to handle two combobox´s and an additional condition.

    My intent:
    I have a Combobox (first one), then i need to add another Combobox (second one) that present only the the unique item´s presents in a column pending the value that i have in the first combobox and another variable present in another column.

    An example takes it easy to understand:

    Category Description Code  Unit price 
    Coats Man C1  $          560,00
    Coats Man C2  $          123,00
    Coats Man C5  $              9,00
    Coats Man C10  $              9,00
    Coats Woman C1  $          456,00
    Coats Woman C6  $            90,00
    Coats Woman C3  $            95,00
    Coats Woman C9  $            95,00
    Hats Man C8  $            34,00
    Hats Man C5  $          450,00
    Hats Man C3  $            89,00
    Hats Man C4  $            89,00
    Hats Woman C8  $            89,00
    Hats Woman C10  $            33,00
    Hats Woman C6  $            66,00
    Hats Woman C4  $            93,00
    Shoes Man C1  $            56,00
    Shoes Man C2  $          156,00
    Shoes Man C5  $            23,00
    Shoes Man C7  $            23,00
    Shoes Woman C1  $            23,00
    Shoes Woman C8  $          134,00
    Shoes Woman C3  $            99,00
    Shoes Woman C5  $            92,00
    Suits Man C1  $          678,00
    Suits Man C6  $          234,00
    Suits Man C7  $            67,00
    Suits Man C4  $            67,00
    Suits Woman C3  $            67,00
    Suits Woman C2  $              5,00
    Suits Woman C5  $            88,00
    Suits Woman C8  $            94,00
    Ties Man C6  $            99,00
    Ties Man C1  $            98,00
    Ties Man C9  $            15,00
    Ties Man C4  $            15,00
    Ties Woman C5  $            15,00
    Ties Woman C2  $            34,00
    Ties Woman C4  $            91,00
    Ties Woman C8  $            96,00

    Take this data, i have a Combobox for the first column (Column A).

    Then, my intent is to built another comboBox that present only the unique values for the Column C if the the following conditions are checked:

    The value select in the first combobox;

    The value in the Column B are "Man" (here i don´t have a Combobox).


    Can you help me to built this VBA code?


    Thank you in advance for the time.

    Saturday, May 7, 2016 2:56 PM

Answers

All replies

  • You didn't say where the comboboxes were located.  If they're located on a worksheet, place the following code within the code module for the sheet (right-click the sheet tab, and select View Code).  If they're located on a userform, place the code within the code module for the userform.  It assumes that your first combobox is named "ComboBox1", and your second combobox is named "ComboBox2".  Change these names accordingly.  It also assumes that "Sheet1" contains the source data.  Change the sheet name accordingly.

    Option Explicit
    
    Private Sub ComboBox1_Change()
        Dim vData As Variant
        Dim aCodes() As String
        Dim lCnt As Long
        Dim i As Long
        Me.ComboBox2.Clear
        If Len(Me.ComboBox1.Value) > 0 Then
            With Worksheets("Sheet1")
                vData = .Range("A1:D" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
            End With
            ReDim aCodes(1 To UBound(vData, 1))
            lCnt = 0
            For i = 1 To UBound(vData, 1)
                If UCase(vData(i, 1)) = UCase(Me.ComboBox1.Value) And UCase(vData(i, 2)) = "MAN" Then
                    lCnt = lCnt + 1
                    aCodes(lCnt) = vData(i, 3)
                End If
            Next i
            If lCnt > 0 Then
                ReDim Preserve aCodes(1 To lCnt)
                Me.ComboBox2.List = aCodes
            End If
        End If
    End Sub

    Hope this helps!


    Domenic Tamburino

    Microsoft MVP - Excel

    xl-central.com - "For Your Microsoft Excel Solutions"


    Saturday, May 7, 2016 4:55 PM
  • Hey Domenic,

    Thanks for the support and well done, it work fine, I only have to ajuste the range in the code to work and then it´s ok.

    One additional question is possible to add a value to the second combobox that doen´st appear in the data base (for example "Total")?

    Thank you in advance for the time.

    Saturday, May 7, 2016 6:37 PM
  • You're very welcome, and thanks for the feedback!

    To add "Total" at the end of the list for the second combobox when there are items that meet both conditions, replace...

            If lCnt > 0 Then
                ReDim Preserve aCodes(1 To lCnt)
                Me.ComboBox2.List = aCodes
            End If

    with

            If lCnt > 0 Then
                ReDim Preserve aCodes(1 To lCnt)
                With Me.ComboBox2
                    .List = aCodes
                    .AddItem "Total"
                End With
            End If

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Saturday, May 7, 2016 7:49 PM
  • Hi again Domenic,

    It works, thanks a lot

    Thank you for you time.

    • Marked as answer by crak1000 Monday, May 16, 2016 9:06 PM
    Saturday, May 7, 2016 8:25 PM
  • You're very welcome!

    Cheers!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Saturday, May 7, 2016 8:59 PM