Pivot Chart Formatting

  • Thread starter Thread starter Bryan Lenker
  • Start date Start date
B

Bryan Lenker

Using Excel 2002 (SP2), whenever I change a pivot field
to update a pivot chart, the formatting on the chart
reverts to its default setting regardless of what I
change it to. Has anyone else had this problem?
 
I will answer my own question...it appears that this
could be a bug in excel. One way to get around it is to
create a macro that applies all of the formatting. If
you want this macro to be driven automatically, call the
macro from the worksheet's change handler (the one that
contains the pivot table). This way, whenever the
worksheet changes, which is whenever the pivot table
changes, the formatting macro runs.
 
I can do the macro, but I'm not sure what you mean
by "call the macro for the worksheet's change handler."
This would be huge if you can explain this to me.! Wow, I
have had this problem for forever. Jennifer
 
Jennifer -

Write the macro that reformats the pivot chart (let's call it
ReformatPivotChart), and put it in a regular code module. Now right
click on the sheet tab, and choose View Code from the pop up menu. At
the top of the code module that appears, select Worksheet from the top
left dropdown, then Change from the top right dropdown. The VB Editor
writes the first and last name of a worksheet change event procedure for
you. You need to supply the code in between. In this case it's easy:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ReformatPivotChart
End Sub

Whenever a value in the worksheet is changed (i.e., when the pivot table
refreshes), the chart will fix itself.

- Jon
 
Back
Top