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