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