PDA

View Full Version : Refresh PivotChart excel freezes



Shazam
06-29-2006, 01:13 PM
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
06-30-2006, 06:38 AM
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