Trouble populating Excel Userform ComboBoxes with Pivot Table Fields.

ajmhouser

New Member
Joined
Jul 27, 2018
Messages
2
I am using Excel 2016 and I have a userform (ComReport) that when clicked the user has two combo boxes to choose what they want to filter, Year & Quarter. Unfortunately I have not been able to get the combo boxes to fill. I have searched online and tried different tweaks of pretty much the same code, unfortunately did not save every attempt. Here is the code that I have that isn't working and needed guidance:

Option Explicit
Private Sub ComReport_Initialize()

Dim sheet As Worksheet
Dim pt As PivotTable

Set sheet = ThisWorkbook.Worksheets("PTComReport")
Set pt = sheet.PivotTables("ComReport")

Dim pf As PivotField

Set pf = pt.PivotFields("Year")

Dim item As PivotItem
Dim index As Integer
index = 1

For Each item In pf.PivotItems
Me.cboPTYear.AddItem item.Name
Next item

Me.cboPTYear.AddItem "(All)"


Set pf = Nothing

Set pf = pt.PivotFields("Quarter")

For Each item In pf.PivotItems
Me.cboPTQuarter.AddItem item.Name
Next item

Me.cboPTQuarter.AddItem "(All)"
Set pf = Nothing


End Sub
Private Sub cboPTYear_Change()

Dim sheet As Worksheet
Dim pt As PivotTable
Dim pf As PivotField

Set sheet = ThisWorkbook.Worksheets("PTComReport")
Set pt = sheet.PivotTables("ComReport")
Set pf = pt.PivotFields("Year")

pf.CurrentPage = Me.cboPTYear.Value


End Sub
Private Sub cboQuarter_Change()

Dim sheet As Worksheet
Dim pt As PivotTable
Dim pf As PivotField

Set sheet = ThisWorkbook.Worksheets("PTComReport")
Set pt = sheet.PivotTables("ComReport")
Set pf = pt.PivotFields("Quarter")

pf.CurrentPage = Me.cboPTQuarter.Value

End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top