Populate a combobox with values from a pivot table [VBA]
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
- Go to VB Editor (Alt + F11)
- Double press with left mouse button on Sheet4 (Sheet1) in Project Explorer, see image above. This is the sheet where ComboBox1 is located.
- Paste code to code module.
Combobox category
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
In this tutorial I am going to explain how to: Create a combo box (form control) Filter unique values and […]
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this blog article, I will demonstrate basic file copying techniques using VBA (Visual Basic for Applications). I will also […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
Excel categories
8 Responses to “Populate a combobox with values from a pivot table [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
The zip file has a buch of files but non is excel 2007 .xlsm. Please repost. Thanks.
John Tseng,
That is weird.
I opened the file unique-values-combobox-pivot-table.xlsm and it is an excel 2007 macroenabled file *.xlsm.
There are no zip files in this post?
Hi Oscar,
Thaks for the code, it works great. Could you add a third dependent combo box to this? i.e. select search type(country)/ select value (specific country)/select last name
Many thanks
Adam
The file transfer does not work. Neither box get filled.
The only thing I've tried is to delete the existing selection of "Country" from combobox1 and I immediately get error 1004, unable to get pivot field properties.
I then switched to the pivot table and it was gone. ??
Any suggestions?
Allan,
run FillCombo1 macro
1. Go to tab "Developer"
2. Press with left mouse button on "Macros" button
3. Select FillCombo1
4. Press with left mouse button on the "Run" button
Now the first combobox is filled. Select a value and the second combobox is filled.
I can give a file when I get it xml file
Many thanks
could you make this macro to work on excel 2003, thanx.
two combobox are filled but pivot table is not correct in next sheet(sheet2)