Changing Pivot Chart Options Can I?

S

stew

I would like to change some colours in my Pivot charts and other
things such as how heavy a line is and also to be able to mix charts
i.e change it to line and bar chart but every time I look back at my
sheet the chart has reverted. Anyway that I can change this as saving
it makes no odds.

Thanks

Stew
 
R

Roger Govier

Hi Stew

This is a problem with Pivot Charts.
The way around it, is to record a macro as you do the formatting.
Then copy the macro, and place it within a Chart activate event. See the
example below. The code that you record with the macro recorder, will go
within the section I have noted to apply to the particular formatting you
want.

Copy the code in it's entirety after you have created your section>right
click on the chart sheet>View Code>paste the code into the white pane. Press
Alt+F11 to return back to Excel.
Then, whenever you return to the chart, your formatting will be re-created.

Private Sub Chart_Activate()
On Error Resume Next
Application.ScreenUpdating = False

'' your code will start here
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.ColorIndex = 9
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With

'' your code will end here
ActiveChart.ChartArea.Select
Application.ScreenUpdating = True
On Error GoTo 0
End Sub
 
M

martin.broeren

Hi Roger, I am having the exact same problem, and have used your work
around succesfully! it works great if i jump from one sheet to the
chart sheet. it applies the formatting perfectly.

But...when i change the pivot selection on the chart sheet, but don't
jump to another sheet, it does not apply the formatting. is there a
way to automate that as well. I have been thinking of applying a
shortcut to the macro so it runs upon request, but it would be great
if this can be done automatically.

Any suggestions?

Thanks in advance.


Martin
 
R

Roger Govier

Hi Martin

On the same sheet copy your code (between the Private Sub and End Sub) and
repeat it inside the following event

Private Sub Chart_BeforeRightClick(Cancel As Boolean)

' copy your code in here


End Sub

Then, after making your changes right click on any area outside of the chart
and the formatting will be reset
 
Top