programically refresh pivot chart formatting
 

I am beginningt o embrace pivot charts for faster automated reports. Problem-
when you refresh pivots or data the chart wipes my colors and most important
the dtata point values.

So I must run through massive reports fixing many charts.

How would one approach automating a charts setting - do you build a macro to
work on active sheet or Ative Chart , for like charts can you tell it to fix
multiple sheets.

I woul dbe happy with soemhting affective that woul dneed to be activated on
the active chart - it would still save a great deal of time.

Thanks for your help and suggestions or examples would be appreciated.

Todd

programically refresh pivot chart formatting
 

Hi Todd

This may help a little...

Sub PivotChartFormat()
Dim ch As Chart, i As Long
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False

For Each ch In ActiveWorkbook.Charts
With ch
..SizeWithWindow = True
..ChartArea.AutoScaleFont = True
..ChartType = xlColumnStacked
..HasTitle = False
..HasLegend = True

With .ChartArea
With .Font
..Name = "Tahoma"
..Size = 10
End With
..AutoScaleFont = False
End With

With .PlotArea
With .Border
..ColorIndex = 16
..Weight = xlThin
..LineStyle = xlContinuous
End With
..Fill.TwoColorGradient Style:=msoGradientHorizontal, Variant:=4
..Fill.Visible = True
..Fill.ForeColor.SchemeColor = 4
..Fill.BackColor.SchemeColor = 1
End With

With .Axes(xlCategory)
..MajorTickMark = xlOutside
..MinorTickMark = xlNone
..TickLabelPosition = xlLow
..Border.LineStyle = xlNone
With .TickLabels
..Alignment = xlCenter
..Offset = 100
..AutoScaleFont = False
End With
End With

With .Axes(xlValue)
With .MajorGridlines
With .Border
..ColorIndex = 57
..Weight = xlHairline
..LineStyle = xlDot
End With
End With
With .TickLabels
..AutoScaleFont = False
..Font.Bold = False
..NumberFormat = "#,##0 ;[Red](#,##0)"
End With
End With

For i = 1 To .SeriesCollection.Count
With .SeriesCollection(i)
..Border.LineStyle = xlNone
..ApplyDataLabels AutoText:=True, ShowValue:=True
..DataLabels.Font.ColorIndex = 2
..DataLabels.Font.Bold = True
End With
Next i

For i = 1 To .ChartGroups.Count
With .ChartGroups(i)
..Overlap = 100
..GapWidth = 25
..HasSeriesLines = False
End With
Next i

..Legend.Position = xlTop
..HasDataTable = True
..DataTable.AutoScaleFont = False
..DataTable.Font.Bold = False
..DataTable.Font.Size = 10
End With

Next ch

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub



--

Regards

William

XL2003




"Todd F." wrote in message
...
|I am beginningt o embrace pivot charts for faster automated reports.
Problem-
| when you refresh pivots or data the chart wipes my colors and most
important
| the dtata point values.
|
| So I must run through massive reports fixing many charts.
|
| How would one approach automating a charts setting - do you build a macro
to
| work on active sheet or Ative Chart , for like charts can you tell it to
fix
| multiple sheets.
|
| I woul dbe happy with soemhting affective that woul dneed to be activated
on
| the active chart - it would still save a great deal of time.
|
| Thanks for your help and suggestions or examples would be appreciated.
|
| Todd

programically refresh pivot chart formatting
 

thank you for your time - your right it is a start now I am off to study it.
I am a light weight but learning I am following ing what I am reading.

I know how to request more then I can write.

Again thank you - this is a good base line

"William" wrote:

Hi Todd

This may help a little...

Sub PivotChartFormat()
Dim ch As Chart, i As Long
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False

For Each ch In ActiveWorkbook.Charts
With ch
..SizeWithWindow = True
..ChartArea.AutoScaleFont = True
..ChartType = xlColumnStacked
..HasTitle = False
..HasLegend = True

With .ChartArea
With .Font
..Name = "Tahoma"
..Size = 10
End With
..AutoScaleFont = False
End With

With .PlotArea
With .Border
..ColorIndex = 16
..Weight = xlThin
..LineStyle = xlContinuous
End With
..Fill.TwoColorGradient Style:=msoGradientHorizontal, Variant:=4
..Fill.Visible = True
..Fill.ForeColor.SchemeColor = 4
..Fill.BackColor.SchemeColor = 1
End With

With .Axes(xlCategory)
..MajorTickMark = xlOutside
..MinorTickMark = xlNone
..TickLabelPosition = xlLow
..Border.LineStyle = xlNone
With .TickLabels
..Alignment = xlCenter
..Offset = 100
..AutoScaleFont = False
End With
End With

With .Axes(xlValue)
With .MajorGridlines
With .Border
..ColorIndex = 57
..Weight = xlHairline
..LineStyle = xlDot
End With
End With
With .TickLabels
..AutoScaleFont = False
..Font.Bold = False
..NumberFormat = "#,##0 ;[Red](#,##0)"
End With
End With

For i = 1 To .SeriesCollection.Count
With .SeriesCollection(i)
..Border.LineStyle = xlNone
..ApplyDataLabels AutoText:=True, ShowValue:=True
..DataLabels.Font.ColorIndex = 2
..DataLabels.Font.Bold = True
End With
Next i

For i = 1 To .ChartGroups.Count
With .ChartGroups(i)
..Overlap = 100
..GapWidth = 25
..HasSeriesLines = False
End With
Next i

..Legend.Position = xlTop
..HasDataTable = True
..DataTable.AutoScaleFont = False
..DataTable.Font.Bold = False
..DataTable.Font.Size = 10
End With

Next ch

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub



--

Regards

William

XL2003




"Todd F." wrote in message
...
|I am beginningt o embrace pivot charts for faster automated reports.
Problem-
| when you refresh pivots or data the chart wipes my colors and most
important
| the dtata point values.
|
| So I must run through massive reports fixing many charts.
|
| How would one approach automating a charts setting - do you build a macro
to
| work on active sheet or Ative Chart , for like charts can you tell it to
fix
| multiple sheets.
|
| I woul dbe happy with soemhting affective that woul dneed to be activated
on
| the active chart - it would still save a great deal of time.
|
| Thanks for your help and suggestions or examples would be appreciated.
|
| Todd