I've been expermenting with pivot charts. They work great, but all you can get when using a bar chart is a blue bar. I tried reformatting the chart, changing the bar colors, adding values - until it looks great. But once i refresh the data - I lose everything and am left with the default blue bars. Is there a work around ?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Is there any way to make pivot charts look better
- Thread starter MJK
- Start date
Use this code and change the color code to your desire.
Code:
Private Sub Chart_Calculate()
Dim intSeries As Integer
Dim intPoint As Integer
With ActiveChart
For intSeries = 1 To .SeriesCollection.Count
With .SeriesCollection(intSeries)
Select Case intSeries ' may need to extend cases
Case 1
.Interior.ColorIndex = 41
End Select
.ApplyDataLabels
.DataLabels.Position = xlLabelPositionInsideEnd
.DataLabels.Font.Bold = True
For intPoint = 1 To .Points.Count
End Select
.DataLabels(intPoint).Top = .DataLabels(intPoint).Top - 20
Next
End With
Next
End With
End Sub
Upvote
0
that is a very fustrating feature - i have to record a macro of all the formatting changes required for each chart then assign said macro to a button so whenever the pivot tables get refreshed, its then a simple button push to get the charts back looking nice.
its a big pain too
i will try that code sometime vane
its a big pain too
i will try that code sometime vane
Upvote
0
I have a couple of questions and need some direction
I pasted the code into a module. Changed the formatting of the pivot chart, and refreshed the data. The chart reverted back to the default blue bars. I looked to run the macro from Tools> Macros and there were none listed. Went back to the VBE and ran the macro and got a Compile Error: End Select without Select Case. What did I do wrong ?
Thanks again for the help
MJK
I pasted the code into a module. Changed the formatting of the pivot chart, and refreshed the data. The chart reverted back to the default blue bars. I looked to run the macro from Tools> Macros and there were none listed. Went back to the VBE and ran the macro and got a Compile Error: End Select without Select Case. What did I do wrong ?
Thanks again for the help
MJK
Upvote
0
tusharm
MrExcel MVP
- Joined
- May 28, 2002
- Messages
- 11,029
Yes, that is a downside of pivot charts (and pivot tables). Several formatting options get reset each time the table/chart is refreshed.
What you should do is create custom code that makes the changes you want. To do that, select the PC, then turn on the macro recorder (Tools | Macro > Record new macro...), do the customization you want, and turn off the recorder. Now, earch time you redo the chart, select it and run the code.
Edit: Alternatively, you can do what I do often. Use the data from the pivot table but create my own 'regular' chart. Now, the chart refreshes whenever the PT does but it doesn't lose any formatting -- with the added benefit that there is no code to worry about.
What you should do is create custom code that makes the changes you want. To do that, select the PC, then turn on the macro recorder (Tools | Macro > Record new macro...), do the customization you want, and turn off the recorder. Now, earch time you redo the chart, select it and run the code.
Edit: Alternatively, you can do what I do often. Use the data from the pivot table but create my own 'regular' chart. Now, the chart refreshes whenever the PT does but it doesn't lose any formatting -- with the added benefit that there is no code to worry about.
MJK said:
Upvote
0
I have gone this route in the past, and it works great if you only have a few charts. I have about 500 charts that need to be generated on a monthly basis and pasted into powertpoint. With the help of the board, I have been using a macro with cycles through all the data in a pivot chart, and bulk loads to chart picture into powerpoint. Could I record a chart formatting macro which will loop everytime the pivot is refreshed ?
Thanks for the hlep
MJK
Thanks for the hlep
MJK
Upvote
0
Vane
That's what the problem was - I moved my pivot chart to the same sheet where my pivot table resides. The code worked great when the pivot chart is on its oun sheet.
Is there a way of modifying the code so it will work when to pivot chart is moved to the same sheet as the pivot ?
Thanks
MJK
That's what the problem was - I moved my pivot chart to the same sheet where my pivot table resides. The code worked great when the pivot chart is on its oun sheet.
Is there a way of modifying the code so it will work when to pivot chart is moved to the same sheet as the pivot ?
Thanks
MJK
Upvote
0
Similar threads
- Question
