Thread: Keep losing percentages on Pivot Chart

In the attached Excel workbook, I’ve created a Pivot Chart.

I keep on losing the percentages that sit on top of the bar charts, every time I change the filter on the service pivot

Is it possible to maintain them for each individual service, so their percentages are as clear as possible?

Hi there,

This seems to be a known issue, as Microsoft is referring to it in this article :http://support.microsoft.com/kb/215904

Hope this helps

J.

thanks jevni1974

that link was great... a solution to this problem is adding a macro that record the actions of changing the format... which i have done successfully... but unfortunately the control toolbox doesn't allow me to select command button as it is shodowed out (deselected)

hope you can help

thanks in advance

Okay...so one step closer again...but still a step behind

can you post your updated excel file so I can have a look?

hi mate,

attached is the sample, i've recorded a macro that adds the formating back to the Pivot chart

Well....

Okay... have a look at the sheet now.
I added a rectangle to which I assigned a macro (have a look at the popup menu when you right-click on the rectangle)

I guess that works fine as a command button right?

J.

cheers mate you a star

Hi mate...

I'm asking a little favour, i'm just being a bit picky, i'm wondering instead of adding an Autoshape to the Pivot Chart, is it possible to create a drop down combo box which is similar to the one already on the sheet, and when a selection is made the label is added automatically....

i hope i havent confused you... please come to me for more clarification (if need)

thanks in advance

you a star... its perfect... its exactly what i needed.... for my problem

do you mind if i ask how you did it... would like to know how your miracles are done... lol

if you have time is...

can you point me to a peice of code...

thanks again

thanks... something I could look into Event Procedures... they are a great feature to learn... thanks again....

Whats the best to learn VBA... its a great tool to have

Hi jevni1974,

Thanks for that mate,

Having incorporated the VBA code into my worksheet, I've noticed that it applies the formatting to every single PivotChart, (All of my pivot tables are stored in a single worksheet)

In the document I have a number of different Graphs that I wish not to add the formatting too.

Is it possible to alter the code so I can state which PivotTables I would like to incorporate.

I've tried modifying the code so it will incorporate this.... Unfornately it doesn't compile correctly.

Code:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    ActiveSheet.PivotTable("PivotTable13").ApplyDataLabels AutoText:=True, LegendKey:= _
    False, ShowSeriesName:=False, ShowCategoryName:=False, ShowValue:=True, _
    ShowPercentage:=False, ShowBubbleSize:=False
End Sub

a quick solution to that problem was to remove the pivot tables that I didn't need the formatting applied to and place them onto a seperate sheet...

as all the pivot tables are hidden from view, it wouldn't matter what page they where on...

thanks again