(Excel 2002) Formatting Lost on Pivot Chart Update

Question

I have a set of Pivot tables/charts automatically created from VBA code with custom formatting.

When I change the selection of data to be displayed from the drop down list in the chart, the custom formatting reverts back to the chart type's default display. The underlying chart data is not changed, only what's displayed on the chart. What do I have to do to retain that custom formatting everytime I change the pivot item selection in a row field?

Edit 1: The underlying data for the pivot table does not change. However, everytime I change a filter on a row, column or page field, the updated pivot chart loses the original format set by the VBA code.

Edit 2: This only pertains to Excel versions older than Excel 2007.

Answer 1

What it sounds like to me is that the VBA code generates the pivot table/chart and then you are trying to tweak it afterwards. Unfortunately with VBA, when you update the pivot table/chart, all your custom formatting is lost. Excel is basically creating a new one each time as well.

If you did the formatting by hand in Excel, I think Excel keeps track of that and will keep your formatting as you change the data. The example I can think of is if you accidentally delete a sheet you an Undo it, however if you delete a sheet in VBA there's no possible way to Undo.

I would try this as a solution.

Create two procedures: one that creates a PivotTable from scratch when given data, and the other that formats a pivot table when given the range of a pivot table. The code could look something like this

Sub GeneratePivotTable(rng as Range, var1 as Variant, var2 as Variant)
    ' Code here that makes your Pivot Table
    ' pass whatever you need to get the job done
End Sub

Sub FormatPivotTable(rng as Range, var1 as Variant, var2 as Variant)
    ' Code here to format pivot table given range and other information
    ' You might even be able to just directly pass a PivotTable object :D
End Sub

Then all you would have to do next to make it functional like your current code is create a wrapper function that just calls both.

Sub GenerateAndFormatPivotTable(rng as Range, var1 as Variant, var2 as Variant)
    Call GeneratePivotTable(rng, var1, var2)
    ' Maybe some processing here to set up the next call
    Call FormatPivotTable(rng, var1, var2)
End Sub

If you wanted to pass the PivotTable as an object you could do something neat like....

Function GeneratePivotTable(variables as Variant) as PivotTable
    ' Generate your pivot table and return it
    GeneratePivotTable = myPivotTableThatIMade
End Function

Sub FormatPivotTable(aPivotTableThatYouMake as PivotTable)
    ' Code that formats your PivotTable
End Sub

Sub GenerateAndFormatPivotTable(rng as Range, var1 as Variant, var2 as Variant
    Call FormatPivotTable(GeneratePivotTable(variables as Variant)
End Sub

You do all this so if you make a table, then have to tweak it, you can use that FormatPivotTable to format the updated table.

For extra madness, have the sheet that has the PivotTable fire it's Worksheet.Change or Worksheet.Activate event to search for a PivotTable and call your formatting function :D