Keep losing percentages on Pivot Chart

[akhlaq768]

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?

[jevni1974]

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.

[akhlaq768]

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

[jevni1974]

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

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

[akhlaq768]

hi mate,

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

[jevni1974]

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.

[akhlaq768]

cheers mate you a star

[akhlaq768]

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

[jevni1974]

Hi there.... picky??? neah some people just want the perfect solution I guess....

a dropdown combobox is a control like a command button...
which can not be added to the pivot table chart....
so this is not possible I'm afraid.

an alternative would be to look if there is a VBA construction possible that catches the events taking place in the pivot chart and run the macro based on these...

Since a pivot chart is based on a pivot table and there is a VBA event called 'Worksheet_PivotTableUpdate' I created an event procedure in the module of sheet 2 (Pivot Data) and put the macro code in the event procedure.

the result is attached :D

Have fun

[akhlaq768]

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

[jevni1974]

Hi ... good to see you're happy at last

you can find the code the following way:
1. select Tools -> Macro -> Visual basic editor (or press ALT F11)
2. double-click on Sheet 2 (Pivot Data) (see attached picture as well)

this will open up the VBA Module belonging to Sheet 2.
That is where the code should be as the pivot table of the chart is on Sheet 2 and the code is activated through the worksheet event 'PivotTableUpdate'.

This event takes place if the pivot table is changed. And that is what happens when you make selections in the pivot chart.

Hope this helps

[akhlaq768]

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

[akhlaq768]

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.

Please Login or Register  to view this content.

[akhlaq768]

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

[jevni1974]

Hi there,

the Worksheet_PivotTableUpdate event applies to all pivot tables that are located on the specific worksheet.

Indeed the solution would be to move the pivottables you don't want to change to another sheet. on tat other sheet you can then define again a Worksheet_PivotTableUpdate event procedure that may be doing other things for you.

Have fun :D