Attempting VBA to refresh "Chart Title" based on Pivot Filters. Filter1 works in VBA, but title doesn't update based on filter2.

[ghull99]

The following is my VBA Code:

Sub UpdateChartTitle()
Dim pt As PivotTable
Dim pf As PivotField
Dim ws As Worksheet
Dim chartTitle As String
Dim filter1 As String
Dim filter2 As String

' Set the worksheet and pivot table
Set ws = ThisWorkbook.Sheets("Top Overall & EU Complaints") ' Change to your sheet name
Set pt = ws.PivotTables("PivotTable34") ' Change to your pivot table name

' Loop through each pivot field in the pivot table
For Each pf In pt.PivotFields
If pf.Orientation = xlPageField Then
If pf.Name = "Region" Then ' Change to your first filter field name
Debug.Print "Region: [" & pf.CurrentPage & "]" ' Debugging line
If Trim(pf.CurrentPage) = "(All)" Then
filter1 = "Worldwide" ' Replace with "Worldwide"
Else
filter1 = pf.CurrentPage
End If
Debug.Print "Filter1 Result: " & filter1 ' Debugging line
ElseIf pf.Name = "Name" Then ' Change to your second filter field name
Debug.Print "Name: [" & pf.CurrentPage & "]" ' Debugging line
If Trim(pf.CurrentPage) = "(All)" Then
filter2 = "All Products" ' Replace with "All Products"
Else
filter2 = pf.CurrentPage
End If
End If
End If
Next pf

' Combine filters into the chart title
chartTitle = filter1 & " Top Complaint Types for " & filter2

'Set the chart title
ws.ChartObjects("Chart 3").chart.chartTitle.text = chartTitle 'Change to your chart name
End Sub
Sub RefreshChartTitle()
' Call the UpdateChartTitle subroutine whenever the pivot table is updated
Call UpdateChartTitle
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$3" Then
MsgBox ("Region Has Changed.")
End If
If Target.Address = "$C$5" Then
MsgBox ("Product Name Has Changed.")
End If
End Sub