Refresh PivotChart excel freezes

[Shazam]

Well this is weird. I have a PivotChart and we all know that you will loose your formatting when updating the PivotChart. So I created a simple macro and put it in a worksheet event code. Its fine now but when I try to refresh the pivotchart excel freezes. Has anyone found a solution to this problem?


Here is the worksheet event code to retain my formatting.

 
Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.SeriesCollection(1).Select
    With Selection.Border
        .ColorIndex = 3
        .Weight = xlThick
        .LineStyle = xlGray75
    End With
    With Selection
        .MarkerBackgroundColorIndex = 1
        .MarkerForegroundColorIndex = 1
        .MarkerStyle = xlDash
        .MarkerSize = 9
    End With
    ActiveChart.SeriesCollection(2).Select
    With Selection.Border
        .ColorIndex = 3
        .Weight = xlThick
        .LineStyle = xlGray75
    End With
    With Selection
        .MarkerBackgroundColorIndex = 1
        .MarkerForegroundColorIndex = 1
        .MarkerStyle = xlDash
        .MarkerSize = 9
    End With
    ActiveChart.SeriesCollection(3).Select
    With Selection.Border
        .Weight = xlThin
        .LineStyle = xlAutomatic
    End With
    With Selection
        .MarkerBackgroundColorIndex = 1
        .MarkerForegroundColorIndex = 1
        .MarkerStyle = xlCircle
        .MarkerSize = 5
        End With
    ActiveWindow.Visible = False
    Windows(ActiveWorkbook.Name).Activate
 
End Sub

[Shazam]

Well I got it to work. I believe you can't refresh the pivot chart when having a worksheet event code for the pivot table data. But I recorded this macro and put it into a Workbook_ Open event code and it works fine. So when ever I open the workbook it refreshes the pivot chart without giving me a debugger.

 
Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    ActiveSheet.EnableCalculation = False
 
 
    Sheets("Chart").Select
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.PivotLayout.PivotTable.RefreshTable
    ActiveWindow.Visible = False
    Windows(ThisWorkbook.Name).Activate    
    Sheets("System").Select
 
    ActiveSheet.EnableCalculation = True
    Application.ScreenUpdating = True
 
   End Sub