Pivot Chart problems

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Harley Feldman - 16 Dec 2004 19:09 GMT

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

Reply to this Message

Debra Dalgleish - 16 Dec 2004 21:41 GMT

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.

> I have two issues with pivot charts.
>
[quoted text clipped - 22 lines]
>
> Harley

Signature

Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply to this Message

Harley Feldman - 17 Dec 2004 04:19 GMT

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

Reply to this Message

Debra Dalgleish - 17 Dec 2004 04:40 GMT

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

> Debra,
>
[quoted text clipped - 10 lines]
>
> Harley

Signature

Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply to this Message

Harley Feldman - 17 Dec 2004 05:38 GMT

Debra,

Perfect! I apparently did not ready your response correctly.

Thanks,

Harley

Reply to this Message

Harley Feldman - 17 Dec 2004 18:16 GMT

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

Reply to this Message

Harley Feldman - 17 Dec 2004 20:11 GMT

Never mind. I figured it out

Reply to this Message