In this post I am going to demonstrate two things:

• How to populate a combobox based on column headers from an Excel defined Table
• 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, see image above.

'Name macro
Sub FillCombo1()

'Declare variables and data types
Dim Cell As Range
Dim i As Single
Dim shpobj As Object

'Assign Table1 to object Cell
Set Cell = Worksheets("Sheet1").Range("Table1")

'Clear values in ComboBox1 
Worksheets("Sheet1").ComboBox1.Clear

'Go through each column in object Cell (Table1)
For i = 1 To Cell.ListObject.Range.Columns.Count

    'Add column header value to combobox
    Worksheets("Sheet1").ComboBox1.AddItem Cell.ListObject.Range.Cells(1, i)

'Continue with next column
Next i

End Sub

Populate a combobox with unique values using a PivotTable

The selected value in the combobox to the left determines which 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.

'Name macro
Sub FillCombo2()

'Declare variables and data types
Dim i As Single, r As Single
Dim shpobj As Object
Dim rng As Variant, Value As Variant

'Don't show changes
Application.ScreenUpdating = False

'Refresh PivotTable
Worksheets("Sheet2").PivotTables("PivotTable1").PivotCache.Refresh

'Ignore errors
On Error Resume Next

'Hide all fields in PivotTable
For Each ptfl In Worksheets("Sheet2").PivotTables("PivotTable1").PivotFields
    ptfl.Orientation = xlHidden
Next ptfl

'Don't ignore errrors
On Error GoTo 0

'Go through all OLEObjects (ActiveX) in Sheet1
For Each shpobj In Worksheets("Sheet1").OLEObjects

    'Find ComboBox1 and make sure it is not empty if so exit For ... Next statement
    If shpobj.Name = "ComboBox1" And shpobj.Object.Value = "" Then Exit For
    
    'Use the value in ComboBox1 to extract unique distinct values in PivotTable
    If shpobj.Name = "ComboBox1" Then
        Worksheets("Sheet2").PivotTables("PivotTable1") _
        .PivotFields(shpobj.Object.Value).Orientation = xlRowField
        Worksheets("Sheet2").Select

        'Make sure that the Pivot table is recalculated
        Application.Calculate
    End If
    
    'Check if shpobj is Combobox2
    If shpobj.Name = "ComboBox2" Then
         
        'Clear comboobox values
        shpobj.Object.Clear

        'Populate ComboBox2 with values from PivotTable
        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

'Activate Sheet1
Worksheets("Sheet1").Select

'Show changes for the user
Application.ScreenUpdating = True

End Sub

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

Private Sub ComboBox1_Change()

Call FillCombo2

End Sub

How to add code to sheet module

1. Go to VB Editor (Alt + F11)
2. Double press with left mouse button on Sheet4 (Sheet1) in Project Explorer, see image above. This is the sheet where ComboBox1 is located.
3. Paste code to code module.

Get the Excel file

unique-values-combobox-pivot-table.xlsm

Combobox category

Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]

Working with COMBO BOXES [Form Controls]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]

Change PivotTable data source using a drop-down list
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]

Macro category

Save invoice data [VBA]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]

Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]

Excel categories

Comments (8)