change chart type on pivot refresh

[Guest]

I'd like to have a pivotchart which is the line on 2 axes type, but every
time I change something in the pivotchart, it changes back to its default.
Is there some code that I can have which works on any refresh/change of the
chart, which would automatically make the chart type go back to line on 2
axes?

Thanks.

 

[Debra Dalgleish]

You could record the steps as you manually apply the chart type.
Then, add that code to the Worksheet_PivotTableUpdate event.

 

[Guest]

Got the basic code. What is the worksheet_pivotableupdate event, or how do I
invoke it?

Thx.

 

[Debra Dalgleish]

It's a worksheet event in Excel 2002 and Excel 2003.

Right-click the sheet tab on the pivot table worksheet, and choose View
Code.
From the dropdown list at the top left of the code module, choose
Worksheet.
From the dropdown list at the top right of the code module, choose
PivotTableUpdate.
Add your code to this event.

 

[Guest]

This is specifically for a pivotchart, for which I did find the worksheet,
and for which I found the "chart_calculate" function. Unfortunately when I
put my macro in there to update the type and color of line, and I change the
value of the chart (select a diferent page field), it seems to go in a loop
of the macro. My guess is because the change of the chart happens, it
triggers the macro, because the macro changes something (although doesn'
actually calculate, just changes colors), the macro triggers, and so on.

Thx for any further help.

 

[Debra Dalgleish]

You could disable events at the start of the code, and enable them at
the end:

Application.EnableEvents = False
'your code
Application.EnableEvents = True

 

[Guest]

As always, perfect solution. Thx.
--
Boris

You could disable events at the start of the code, and enable them at
the end:

Application.EnableEvents = False
'your code
Application.EnableEvents = True

 

[Debra Dalgleish]

You're welcome.

As always, perfect solution. Thx.

 

[Guest]

Debra, came across a reference to some of your code, and was wondering if you
could help me adjust it just a bit, if even possible to do...

I'd like to have something that cycles through the page fields in a
pivorchart. In other words, if there are 3 items in page field 1, and 4 in
page field 2, I'd want it to take:

field 1 field 2
"all" "all"
"all" item 1
"all" item 2
"all" item 3
item 1 "all"
item 1 1st item

etc., until it gets through each of the first field and the second field. I
would combine this with other code which, inbetween the cycle to the next
option, would grab the chart and paste it elsewhere, but all I need is to
cycle through the page fields in the above fashion.

I'd posted this in programming and Dave Peterson responded with a link to
your code:


http://contextures.com/xlPivot09.html#Page

I know yours was set up for one page field. Was that by choice, or because
that's a limitation?

Also, Is there a way to adapt the code to be a pivotchart, as opposed to the
table? I'd thougth maybe there's a way to get the focus on the pivotchart,
but then have the code figure out which underlying table it is, and change
it, without going to that page.

Finally, any chance you know how to get Excel to punt something over to word
and simply paste it?

Thanks for any help. Sorry for the length here.

 

[Debra Dalgleish]

The second example on the page that Dave suggested:

http://contextures.com/xlPivot09.html

is for a chart, and the third example is for multiple page fields.