Changing Pivot Chart Options Can I?

  • Thread starter Thread starter stew
  • Start date Start date
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
 
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
 
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
 
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
 
Back
Top