CODEWITHSUNDEEP
excelcomboboxfiltervba
Tyler
Tyler

Reputation: 45

Excel VBA - Populate a Combobox given selection of another Combobox

I am trying to populate a second combobox given the selection of a first combobox. The first combobox is the name of all the columns on the sheet. The second combobox should display all of the values in that column except for duplicates. The code below populates combobox1. I am struggling with how to take data out of a column given the varying column name. Thank you for any help.

Dim myArray As Variant

lastcol = Sheets(4).Range("A1").End(xlToRight).Column
With Sheets(4)
Set SourceRng = .Range(.Cells(1, 1), .Cells(1, lastcol))
End With
myArray = WorksheetFunction.Transpose(SourceRng)

With Me.ComboBox1
.List = myArray
End With

Upvotes: 1

Views: 7947

Answers (1)

jDave1984
jDave1984

Reputation: 972

You could try to get the listindex of combobox1. Keep in mind that the ListIndex is 0-Based while Excel Rows and Columns are not:

Private Sub ComboBox1_AfterUpdate()

Dim selectedCol as Variant
selectedCol = Me.ComboBox1.ListIndex + 1

Set SourceRng = ws.Range(Cells(2, selectedCol), Cells(4, selectedCol))

Me.ComboBox2.List = WorksheetFunction.Transpose(SourceRng)

End Sub

To get rid of the duplicate values and junk: Set SourceRng = ws.Range(Cells(varRow, Me.ComboBox1.ListIndex + 1), Cells(varRow2, Me.ComboBox1.ListIndex + 1)).RemoveDuplicates Columns:= selectedCol, Header:=xlNo

Here is a workaround for removing the duplicates. Using the RemoveDuplicates function of the Range class will delete your rows, and I'm assuming you don't want that:


Private Sub ComboBox1_AfterUpdate()

    Dim colSelect As Integer
    colSelect = Me.ComboBox1.ListIndex + 1

    Set SourceRng = ws.Range(Cells(2, colSelect), Cells(5, colSelect))

    SourceRng.Sort SourceRng, xlDescending

    Dim c
    For Each c In SourceRng.Cells
        If c.Value  c.Offset(-1, 0).Value Then
            Me.ComboBox2.AddItem c.Value
        End If
    Next

    'You would take out the array and assigning it to the Combobox2.List

End Sub

Upvotes: 1

Related Questions