Is there any way to make pivot charts look better

[MJK]

I've been expermenting with pivot charts. They work great, but all you can get when using a bar chart is a blue bar. I tried reformatting the chart, changing the bar colors, adding values - until it looks great. But once i refresh the data - I lose everything and am left with the default blue bars. Is there a work around ?

 

[MJK]

Jon

I'm using Vanes original code, and it works great at reapplying the values each time the pivot refreshes. For some reason, I cannot get the bar colors to stay upon refresh. I even recorded an macro where I change the bar colors abd pasted it into Vanes code, and it still reverted back to the original blue default colors.

Any help would be greatly appreciated

MJK

Private Sub Chart_Calculate()
Dim intSeries As Integer
Dim intPoint As Integer
With ActiveChart
For intSeries = 1 To .SeriesCollection.Count
With .SeriesCollection(intSeries)
Select Case intSeries ' may need to extend cases
Case 1
.Interior.ColorIndex = 41
End Select
.ApplyDataLabels
.DataLabels.Position = xlLabelPositionInsideEnd
.DataLabels.Font.Bold = True
For intPoint = 1 To .Points.Count
End Select
.DataLabels(intPoint).Top = .DataLabels(intPoint).Top - 20
Next
End With
Next
End With
End Sub

 

[vane0326]

Well I copy your code and change this code to a diiferent color code and I hit refresh and the bar turned into a dark purple. So everything is fine. Maybe you got to check your VBA a references are all there. Or maybe you got to start from scratch all over. Somtimes just maybe somewhere in the line when you are trying a bunch of codes it might of corrupted your workbook. It happen to me before.

.Interior.ColorIndex = 29

and i took this out of your code:

End Select

 

[MJK]

Vane

That code worked great with the pivot chart on its own sheet.

Is there a way to get it to work with the pivot table and chart on the same page ?

Thanks for the help

MJK

 

[Jon Peltier]

You can tie the code into a worksheet_calculate event procedure, or build a class module to capture embedded chart events, and run the Chart_Calculate event procedure from the class module.

 

[vane0326]

You can tie the code into a worksheet_calculate event procedure, or build a class module to capture embedded chart events, and run the Chart_Calculate event procedure from the class module.

Jon Peltier,

I'm curios how the class modules work. So If I build a class module that has a Chart event the code will work off the whole workbook ? or just the worksheet ?

If you have any examples that will be great.

 

[Jon Peltier]

The class module contains property, method, and event code for a class. Somewhere in the program you have to instantiate the class (create it), so the program knows to respond to an event from the class.

I wrote an article about chart event class modules:

http://www.computorcompanion.com/LPMArticle.asp?ID=221

Although I mentioned the Chart_Calculate event procedure as an option, Worksheet_Calculate is going to be easier, because the worksheet is instantiated by the very fact that it's there.

 

[MJK]

Jon

I'm trying to integrate Vanes code into the code you gave me a week ago, which cycles through the pivot table and mass populates powerpoint from the pivot chart. Should I look to add Vanes code (Chartstopresentation) or does it go into a separate module ? The formatting needs to be reapplied each time the pivot refreshes with new data. Is this possible to do ?

BTW - Great article

Thanks for the help
MJK

 

[Jon Peltier]

I would put Vane's module into a regular code module, and call it with a one-liner from the worksheet's _Change event procedure. This way, it's available to any other worksheet that may want to call it. A convenient place to put the chart reformatting code might be within Vane's procedure, right before copying each chart.

 

[MJK]

Jon

Can you give me a little bit more guidance on what needs to be done.

Thanks
MJK

 

[Jon Peltier]

In a regular module, put this code (or whatever it's evolved into):

Sub ColorChartSeries(cht As Chart) 
    Dim intSeries As Integer 
    Dim intPoint As Integer 
    With cht
       For intSeries = 1 To .SeriesCollection.Count 
           With .SeriesCollection(intSeries) 
               Select Case intSeries ' may need to extend cases 
               Case 1 
                   .Interior.ColorIndex = 41 
               End Select 
               .ApplyDataLabels 
               .DataLabels.Position = xlLabelPositionInsideEnd 
               .DataLabels.Font.Bold = True 
               For intPoint = 1 To .Points.Count 
                   .DataLabels(intPoint).Top = .DataLabels(intPoint).Top - 20 
               Next 
           End With 
       Next 
    End With 
End Sub

In the code module for the worksheet that contains the pivot table and chart, use this event procedure:

Private Sub Worksheet_Calculate() 
    ColorChartSeries Me.ChartObjects(1).Chart
End Sub