Pivot Chart problems

[Harley Feldman]

I have two issues with pivot charts.

1) I create a pivot table and chart with VB. I want thick lines on the chart, so I do as follows:

For i = 1 To .SeriesCollection.Count
.SeriesCollection(i).Select
With Selection.Border
.Weight = xlThick
End With
Next

This works great. However, upon selecting a Page Field value from the drop down created automatically on the chart, the new version of the chart has thin lines. Is there a way to tell Excel to create thick lines as a default?

2) I want the pivot chart to use a time scale. If I use the following line:

.Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale

I receive the following message: "Invalid procedure call or argument". If I use this same code on a chart created from a table, I receive no message and it works fine. There must be some problem using pivot charts that I don't understand.

Harley

 

[Debra Dalgleish]

1) Loss of formatting is a known problem with pivot charts. There's
information in the following MSKB article, which using a macro to
reapply the formatting:

XL2000: Changing a PivotChart Removes Series Formatting
http://support.microsoft.com/?id=215904

You could use the Chart_Calculate event to run the formatting code when
the a new page is selected.

2) If you try to do this manually (Chart>Chart Options), you'll see that
time-scale is dimmed when setting options for the Pivot Chart axes.
Since the setting isn't available, your code won't run correctly.

 

[Harley Feldman]

Debra,

I appreciate the response. However, this KB article references a different problem than I was describing. I want to keep the formatting when I click the drop down for the page field at the top of the chart and select a new page field entry. There is no opportunity for code execution from this drop down that I can see. The Excel defaults seem to be used to create the chart once the drop down is used.

On the second item, I saw the same phenomenon that you described. There must be a restriction on pivot charts not being able to use time intervals for the x-axis.

Harley

 

[Debra Dalgleish]

As I mentioned, you can use the Chart_Calculate event to run the code:

Right-click the chart sheet tab, and choose View Code.
Paste the following code where the cursor is flashing:

'====================
Private Sub Chart_Calculate()
Dim s As Series
With ActiveChart
For Each s In .SeriesCollection
s.Border.Weight = xlThick
Next
End With
End Sub
'======================

 

[Harley Feldman]

Debra,

Perfect! I apparently did not ready your response correctly.

Thanks,

Harley

 

[Harley Feldman]

How can I create this routine dynamically from the VB module that generates the pivot chart? This is needed as the pivot chart is deleted and re-created with new pivot table data frequently.

 

[Harley Feldman]

Never mind. I figured it out.