• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

pivot chart lost settings after refresh

D

davecrt

Member
Dear All

Another issue with pivot table

a pivot chart lost its data series formatting settings after i refresh the linked pivot table or i choose a different value in the filter value area. Looking in the forums it looks like it is a well know issue fromm Microsoft. However, someone here has a solution that allows to maintain the settings in the chart also after table refresh or changes in the filter area?

Thanx a lot as ever guys
Bests
Davide
 
Hi davecrt,

I don't know if this will solve your issue, but you can try the following:
  1. Go to the PivotTable Options (either via the Ribbon or right click somewhere on the PivotTable)
  2. Uncheck "Autofit column widths on update"
  3. Check "Preserve cell formatting on update"
  4. ...
  5. Profit?

ExampleE.png
 
No, it does not concern preserve formatting options. It is checked. It is a problem with the lose of formatting. specifically: I have "invert if negative" option checked in a pivot chart, but as soon as i choose another filter value the option get unchecked automatically.. I think need a macro here in order to say to Excel to re-apply the "invert if negative" option each time the basleine pivot is refreshed or another filter value is choosen
 
Sure,here i have attached the file. As you can see in the chart both bars at left and right are green. As solution, i choose the data series option, then do select "invert if negative" option, with four steps and two colour (the settings already in place with the gradient option are founded on another forum and work) and, as a consequence, i get red bars at left and green bars at right. However, in the real sheet (there is some VB and more conditional formatitng a nd more pivot table) if i choose another value in the filter area, then i loose the "invert if negative checkbox".

Therefore i would need a macro that each time pivot is refreshed or i choose another value in the filter area, then the "invert if negative" option flag is reactivated.
Hope to have neen clear in explanation.

At your disposal for further infos
Dave
 

Attachments

  • example.xlsx
    889 KB · Views: 10
Hi Dave ,

I have Excel 2007 ; I have tried out your file by changing the filter options ; the chart retains its formatting each time.

Narayan
 
Yes Narayan. I have excel 2007 too but the file i sent you is simplified. consider that the pivot chart does not retain the formatting each time. how i can teach to excel via macro to get the "invert if negative "option checked after each time pivot table is refreshed??
 
Hi Dave ,

All that is needed is the following statement :

Charts("Chart1").SeriesCollection(1).InvertIfNegative = True

You need to customize it for your chart.

Narayan
 
For the code, i do not have to also declare what is the event that should trigger the code? (i mean, let excel understand that the option must be activated after pivot refresh?)
 
Naraya probably the problem could also be related to the fact that i have a macro that allow to update all pivot tables within the sheet in a coordinated way. the code is here

Option Explicit
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean
On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each pfMain In ptMain.PageFields
bMI = pfMain.EnableMultiplePageItems
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
If ws.Name & "_" & pt <> wsMain.Name & "_" & ptMain Then
pt.ManualUpdate = True
Set pf = pt.PivotFields(pfMain.Name)
bMI = pfMain.EnableMultiplePageItems
With pf
.ClearAllFilters
Select Case bMI
Case False
.CurrentPage = pfMain.CurrentPage.Value
Case True
.CurrentPage = "(All)"
For Each pi In pfMain.PivotItems
.PivotItems(pi.Name).Visible = pi.Visible
Next pi
.EnableMultiplePageItems = bMI
End Select
End With
bMI = False

Set pf = Nothing
pt.ManualUpdate = False
End If
Next pt
Next ws
Next pfMain

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub



so i suppose that probably this code delete the setting for the chart every time a new filter value is choosen. should I entry your code within this routine in order to get the "invert if negative" option checked??

thanx a lot

Bests
Davide
 
Hi Dave ,

Try putting that single line of code ( customized for your chart ) at the end , as follows :

.
.
.
Next pfMain

Charts("Chart1").SeriesCollection(1).InvertIfNegative = True

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

You might have to qualify the reference by including the Worksheet name also , as follows :

Worksheets("Sheet1").ChartObjects("Chart 9").SeriesCollection(1).InvertIfNegative = True

Narayan
 
Option Explicit
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean
On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each pfMain In ptMain.PageFields
bMI = pfMain.EnableMultiplePageItems
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
If ws.Name & "_" & pt <> wsMain.Name & "_" & ptMain Then
pt.ManualUpdate = True
Set pf = pt.PivotFields(pfMain.Name)
bMI = pfMain.EnableMultiplePageItems
With pf
.ClearAllFilters
Select Case bMI
Case False
.CurrentPage = pfMain.CurrentPage.Value
Case True
.CurrentPage = "(All)"
For Each pi In pfMain.PivotItems
.PivotItems(pi.Name).Visible = pi.Visible
Next pi
.EnableMultiplePageItems = bMI
End Select
End With
bMI = False

Set pf = Nothing
pt.ManualUpdate = False
End If
Next pt
Next ws
Next pfMain
Worksheets("DBV YTD").ChartObjects("Chart 4").SeriesCollection(1).InvertIfNegative = True

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub


Hi Naraya. followed your advice but still left bars do no become red, but still green. do you see some error in the code customized??

Bests
Davide
 
You must log in or register to reply here.
Back
Top