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")
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

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
    End If

    If shpobj.Name = "ComboBox2" Then
        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

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