#1   Report Post  
James
 
Posts: n/a
Default Pivot chart issue

I have created a line chart with two Y axises based off of a pivot table.
After the chart is created I customize the text, colors and line widths the
way I want it as well as remove the pivot fields and then save it as the
default chart style. However, whenever the pivot table is refreshed it
changes the chart back to a standard line chart with only one Y axis. Is
there any way to make my chart remain the way I want it after customization
when the source pivot table is refreshed? Thanks!
  #2   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

This is a known problem with PivotCharts. There's information in the
following MSKB article, which suggests recording a macro as you apply
the formatting:

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


James wrote:
I have created a line chart with two Y axises based off of a pivot table.
After the chart is created I customize the text, colors and line widths the
way I want it as well as remove the pivot fields and then save it as the
default chart style. However, whenever the pivot table is refreshed it
changes the chart back to a standard line chart with only one Y axis. Is
there any way to make my chart remain the way I want it after customization
when the source pivot table is refreshed? Thanks!



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

  #3   Report Post  
James
 
Posts: n/a
Default

Thanks for the info. I thought I was going to go crazy! Another fabulous
Microsoft "feature". ;)

"Debra Dalgleish" wrote:

This is a known problem with PivotCharts. There's information in the
following MSKB article, which suggests recording a macro as you apply
the formatting:

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


James wrote:
I have created a line chart with two Y axises based off of a pivot table.
After the chart is created I customize the text, colors and line widths the
way I want it as well as remove the pivot fields and then save it as the
default chart style. However, whenever the pivot table is refreshed it
changes the chart back to a standard line chart with only one Y axis. Is
there any way to make my chart remain the way I want it after customization
when the source pivot table is refreshed? Thanks!



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


  #4   Report Post  
Hannes
 
Posts: n/a
Default

Hello there,

I'm dealing with exactly the same problem.
I have a macro that I want to run every time that the pivot table is changed.
How can I make the pivot table run the macro when it's updated?
Anyone?

Reg. Hannes


"James" wrote:

Thanks for the info. I thought I was going to go crazy! Another fabulous
Microsoft "feature". ;)

"Debra Dalgleish" wrote:

This is a known problem with PivotCharts. There's information in the
following MSKB article, which suggests recording a macro as you apply
the formatting:

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


James wrote:
I have created a line chart with two Y axises based off of a pivot table.
After the chart is created I customize the text, colors and line widths the
way I want it as well as remove the pivot fields and then save it as the
default chart style. However, whenever the pivot table is refreshed it
changes the chart back to a standard line chart with only one Y axis. Is
there any way to make my chart remain the way I want it after customization
when the source pivot table is refreshed? Thanks!



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


  #5   Report Post  
Hannes
 
Posts: n/a
Default

Hi again,

I found out how to do this.

You can use an event called Worksheet_PivotTableUpdate to trigger a macro
that formats your charts.

Regards
Hannes


"Hannes" wrote:

Hello there,

I'm dealing with exactly the same problem.
I have a macro that I want to run every time that the pivot table is changed.
How can I make the pivot table run the macro when it's updated?
Anyone?

Reg. Hannes


"James" wrote:

Thanks for the info. I thought I was going to go crazy! Another fabulous
Microsoft "feature". ;)

"Debra Dalgleish" wrote:

This is a known problem with PivotCharts. There's information in the
following MSKB article, which suggests recording a macro as you apply
the formatting:

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


James wrote:
I have created a line chart with two Y axises based off of a pivot table.
After the chart is created I customize the text, colors and line widths the
way I want it as well as remove the pivot fields and then save it as the
default chart style. However, whenever the pivot table is refreshed it
changes the chart back to a standard line chart with only one Y axis. Is
there any way to make my chart remain the way I want it after customization
when the source pivot table is refreshed? Thanks!


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




  #6   Report Post  
Rav99
 
Posts: n/a
Default

Hannes, can you give me more details how to do this? I am having the same
problem... don't know how to assign that macro to the event procedure. Where
should I do it? From the pivot table, or pivot chart tab?

"Hannes" wrote:

Hi again,

I found out how to do this.

You can use an event called Worksheet_PivotTableUpdate to trigger a macro
that formats your charts.

Regards
Hannes


"Hannes" wrote:

Hello there,

I'm dealing with exactly the same problem.
I have a macro that I want to run every time that the pivot table is changed.
How can I make the pivot table run the macro when it's updated?
Anyone?

Reg. Hannes


"James" wrote:

Thanks for the info. I thought I was going to go crazy! Another fabulous
Microsoft "feature". ;)

"Debra Dalgleish" wrote:

This is a known problem with PivotCharts. There's information in the
following MSKB article, which suggests recording a macro as you apply
the formatting:

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


James wrote:
I have created a line chart with two Y axises based off of a pivot table.
After the chart is created I customize the text, colors and line widths the
way I want it as well as remove the pivot fields and then save it as the
default chart style. However, whenever the pivot table is refreshed it
changes the chart back to a standard line chart with only one Y axis. Is
there any way to make my chart remain the way I want it after customization
when the source pivot table is refreshed? Thanks!


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


  #7   Report Post  
Jon Peltier
 
Posts: n/a
Default

This event is available if you have Excel 2002 or later. Right click on the
worksheet tab of the worksheet with the pivot table, and select View Code. This
opens the VB Editor, with the code module for the worksheet activated. Select
Worksheet from the top left dropdown, then PivotTableUpdate from the top right
dropdown. (I'm only running Excel 2000 on this laptop, so I can't verify that this
works exactly right.)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Rav99 wrote:
Hannes, can you give me more details how to do this? I am having the same
problem... don't know how to assign that macro to the event procedure. Where
should I do it? From the pivot table, or pivot chart tab?

"Hannes" wrote:


Hi again,

I found out how to do this.

You can use an event called Worksheet_PivotTableUpdate to trigger a macro
that formats your charts.

Regards
Hannes


"Hannes" wrote:


Hello there,

I'm dealing with exactly the same problem.
I have a macro that I want to run every time that the pivot table is changed.
How can I make the pivot table run the macro when it's updated?
Anyone?

Reg. Hannes


"James" wrote:


Thanks for the info. I thought I was going to go crazy! Another fabulous
Microsoft "feature". ;)

"Debra Dalgleish" wrote:


This is a known problem with PivotCharts. There's information in the
following MSKB article, which suggests recording a macro as you apply
the formatting:

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


James wrote:

I have created a line chart with two Y axises based off of a pivot table.
After the chart is created I customize the text, colors and line widths the
way I want it as well as remove the pivot fields and then save it as the
default chart style. However, whenever the pivot table is refreshed it
changes the chart back to a standard line chart with only one Y axis. Is
there any way to make my chart remain the way I want it after customization
when the source pivot table is refreshed? Thanks!


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



  #8   Report Post  
Rav99
 
Posts: n/a
Default

OK... Jon, thanks! I got that part now.. but how do I activate the macro to
fix the pivot chart format by this event procedure. I created a pivot chart
but when I changed the filter options, the chart type changes. I need to fix
that as "line-column 2 axes" chart type. Thus what I did is to create a macro
by activiate that chart type, but I don't know where to put the script for
that macro and where to link that to the event procedure. Can you help?

"Jon Peltier" wrote:

This event is available if you have Excel 2002 or later. Right click on the
worksheet tab of the worksheet with the pivot table, and select View Code. This
opens the VB Editor, with the code module for the worksheet activated. Select
Worksheet from the top left dropdown, then PivotTableUpdate from the top right
dropdown. (I'm only running Excel 2000 on this laptop, so I can't verify that this
works exactly right.)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Rav99 wrote:
Hannes, can you give me more details how to do this? I am having the same
problem... don't know how to assign that macro to the event procedure. Where
should I do it? From the pivot table, or pivot chart tab?

"Hannes" wrote:


Hi again,

I found out how to do this.

You can use an event called Worksheet_PivotTableUpdate to trigger a macro
that formats your charts.

Regards
Hannes


"Hannes" wrote:


Hello there,

I'm dealing with exactly the same problem.
I have a macro that I want to run every time that the pivot table is changed.
How can I make the pivot table run the macro when it's updated?
Anyone?

Reg. Hannes


"James" wrote:


Thanks for the info. I thought I was going to go crazy! Another fabulous
Microsoft "feature". ;)

"Debra Dalgleish" wrote:


This is a known problem with PivotCharts. There's information in the
following MSKB article, which suggests recording a macro as you apply
the formatting:

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


James wrote:

I have created a line chart with two Y axises based off of a pivot table.
After the chart is created I customize the text, colors and line widths the
way I want it as well as remove the pivot fields and then save it as the
default chart style. However, whenever the pivot table is refreshed it
changes the chart back to a standard line chart with only one Y axis. Is
there any way to make my chart remain the way I want it after customization
when the source pivot table is refreshed? Thanks!


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




  #9   Report Post  
Jon Peltier
 
Posts: n/a
Default

The macro is called

Worksheet_PivotTableUpdate

which means it's an event of the Worksheet. Right click on the worksheet
tab, and select View Code from the pop up menu. The VB Editor opens, and
a code module appears with the sheet name in the title bar. In the left
dropdown at the top of the code module window, choose Worksheet, and in
the right dropdown, choose PivotTableUpdate. The code module now
contains an empty PivotTableUpdate procedu

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

End Sub

where Target is a variable passed into the procedure which refers to the
pivot table. Put the code into this procedure that you want to run when
the pivot table is updated. (Ignore or delete the empty
Worksheet_SelectionChange procedure.)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Rav99 wrote:
OK... Jon, thanks! I got that part now.. but how do I activate the macro to
fix the pivot chart format by this event procedure. I created a pivot chart
but when I changed the filter options, the chart type changes. I need to fix
that as "line-column 2 axes" chart type. Thus what I did is to create a macro
by activiate that chart type, but I don't know where to put the script for
that macro and where to link that to the event procedure. Can you help?

"Jon Peltier" wrote:


This event is available if you have Excel 2002 or later. Right click on the
worksheet tab of the worksheet with the pivot table, and select View Code. This
opens the VB Editor, with the code module for the worksheet activated. Select
Worksheet from the top left dropdown, then PivotTableUpdate from the top right
dropdown. (I'm only running Excel 2000 on this laptop, so I can't verify that this
works exactly right.)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Rav99 wrote:

Hannes, can you give me more details how to do this? I am having the same
problem... don't know how to assign that macro to the event procedure. Where
should I do it? From the pivot table, or pivot chart tab?

"Hannes" wrote:



Hi again,

I found out how to do this.

You can use an event called Worksheet_PivotTableUpdate to trigger a macro
that formats your charts.

Regards
Hannes


"Hannes" wrote:



Hello there,

I'm dealing with exactly the same problem.
I have a macro that I want to run every time that the pivot table is changed.
How can I make the pivot table run the macro when it's updated?
Anyone?

Reg. Hannes


"James" wrote:



Thanks for the info. I thought I was going to go crazy! Another fabulous
Microsoft "feature". ;)

"Debra Dalgleish" wrote:



This is a known problem with PivotCharts. There's information in the
following MSKB article, which suggests recording a macro as you apply
the formatting:

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


James wrote:


I have created a line chart with two Y axises based off of a pivot table.
After the chart is created I customize the text, colors and line widths the
way I want it as well as remove the pivot fields and then save it as the
default chart style. However, whenever the pivot table is refreshed it
changes the chart back to a standard line chart with only one Y axis. Is
there any way to make my chart remain the way I want it after customization
when the source pivot table is refreshed? Thanks!


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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to lock combination chart in pivot chart? Marcos Novaki Charts and Charting in Excel 1 April 13th 05 10:05 PM
How do I go from "Chart View" to "Pivot Table Form" in a Pivot ta. Jersey Girl Excel Discussion (Misc queries) 1 March 10th 05 05:20 AM
Pivot Chart: cannot apply the default chart type... doco Charts and Charting in Excel 1 January 17th 05 05:55 PM
pivot chart format Valeria Charts and Charting in Excel 1 January 14th 05 02:19 PM
pivot table multi line chart souris Charts and Charting in Excel 2 December 7th 04 04:56 AM


All times are GMT +1. The time now is 03:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"