In this post I am going to demonstrate two things:

  • How to fill a combox with table headers
  • Populate a combobox with unique values using a pivottable

Populate a combobox with table headers

In the picture below you can see a table and two combo boxes. The table has about 50 000 rows.

Let´s populate the combo box to the left with table headers.

 

Sub FillCombo1()
Dim Cell As Range
Dim i As Single
Dim shpobj As Object
Set Cell = Worksheets("Sheet1").Range("Table1")
Worksheets("Sheet1").ComboBox1.Clear
For i = 1 To Cell.ListObject.Range.Columns.Count
    Worksheets("Sheet1").ComboBox1.AddItem Cell.ListObject.Range.Cells(1, i)
Next i
End Sub

 

Populate a combobox with unique values using a pivottable

The selected value in the combobox to the left determines what column is going to be filtered in the pivottable.

The combobox to the right is populated when the pivottable has processed all values.

I think you get the idea when you see the picture below.

Sub FillCombo2()
Dim i, r As Single
Dim shpobj As Object
Dim rng, Value As Variant
Application.ScreenUpdating = False
Worksheets("Sheet2").PivotTables("PivotTable1").PivotCache.Refresh
On Error Resume Next
For Each ptfl In Worksheets("Sheet2").PivotTables("PivotTable1").PivotFields
    ptfl.Orientation = xlHidden
Next ptfl
On Error GoTo 0
For Each shpobj In Worksheets("Sheet1").OLEObjects
    If shpobj.Name = "ComboBox1" And shpobj.Object.Value = "" Then Exit For
    If shpobj.Name = "ComboBox1" Then
        Worksheets("Sheet2").PivotTables("PivotTable1") _
        .PivotFields(shpobj.Object.Value).Orientation = xlRowField
        Worksheets("Sheet2").Select
        Application.Calculate
    End If
    If shpobj.Name = "ComboBox2" Then
        shpobj.Object.Clear
        shpobj.Object.List = Worksheets("Sheet2").PivotTables("PivotTable1").RowRange.Value
        shpobj.Object.RemoveItem 0
        shpobj.Object.RemoveItem _
        Worksheets("Sheet2").PivotTables("PivotTable1").RowRange.Rows.Count - 2
    End If
Next shpobj
Worksheets("Sheet1").Select
Application.ScreenUpdating = True
End Sub

The pivot table is in sheet2. Sheet1 code module also contains some code:

Private Sub ComboBox1_Change()
Call FillCombo2
End Sub

 

Download excel 2007 *.xlsm file

unique values combobox pivot table.xlsm