PDA

View Full Version : Set Pie Chart & Pie Chart Tags to be Same



jo15765
05-09-2012, 07:04 AM
I am working with close to 1000 workbooks, and there are 2 pie charts that are based off of access queries that are refreshed and of course the chart tags change values. I am trying to find a way to write VBA that will once the query has refreshed "build" the pie charts, and set the labels in a "clean" and lined-up order. I recorded a macro and it gave me this code:

Sub t()
Sheets("PieCharts").Activate
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("PieCharts").Range( _
"B28:B36,F28:F36"), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Interior.ColorIndex = xlAutomatic
ActiveChart.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowNone, _
AutoText:=True, LegendKey:=False, HasLeaderLines:=True
With ActiveChart.ChartGroups(1)
.VaryByCategories = True
.FirstSliceAngle = 200
End With
ActiveChart.PlotArea.Select
Selection.Width = 306
Selection.Height = 121
ActiveChart.SeriesCollection(1).ApplyDataLabels Type:= _
xlDataLabelsShowLabelAndPercent, AutoText:=True, LegendKey:=False, _
HasLeaderLines:=True
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.Font.Bold = True
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(1).Points(9).DataLabel.Select
Selection.Left = 339
Selection.Top = 224
ActiveChart.SeriesCollection(1).Points(6).DataLabel.Select
Selection.Left = 463
Selection.Top = 127
ActiveChart.SeriesCollection(1).Points(8).DataLabel.Select
Selection.Left = 456
Selection.Top = 181
ActiveChart.SeriesCollection(1).Points(7).DataLabel.Select
Selection.Left = 483
Selection.Top = 156
ActiveChart.SeriesCollection(1).Points(8).DataLabel.Select
Selection.Left = 445
Selection.Top = 198
ActiveChart.SeriesCollection(1).Points(7).DataLabel.Select
Selection.Left = 460
Selection.Top = 159
ActiveChart.SeriesCollection(1).Points(6).DataLabel.Select
Selection.Left = 464
Selection.Top = 108
ActiveChart.SeriesCollection(1).Points(5).DataLabel.Select
Selection.Left = 330
Selection.Top = 36
ActiveChart.SeriesCollection(1).Points(4).DataLabel.Select
Selection.Left = 54
Selection.Top = 106
ActiveChart.SeriesCollection(1).Points(3).DataLabel.Select
Selection.Left = 63
Selection.Top = 162
ActiveChart.SeriesCollection(1).Points(1).DataLabel.Select
Selection.Left = 215
Selection.Top = 220
ActiveChart.SeriesCollection(1).Points(2).DataLabel.Select
Selection.Left = 147
Selection.Top = 206
ActiveWindow.Visible = False
ActiveSheet.ChartObjects("Chart 9").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Lead Summary").Range( _
"B64:B72,F64:F72"), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
Selection.Interior.ColorIndex = xlAutomatic
ActiveChart.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowNone, _
AutoText:=True, LegendKey:=False, HasLeaderLines:=True
With ActiveChart.ChartGroups(1)
.VaryByCategories = True
.FirstSliceAngle = 200
End With
ActiveChart.PlotArea.Select
Selection.Width = 306
Selection.Height = 121
ActiveChart.SeriesCollection(1).ApplyDataLabels Type:= _
xlDataLabelsShowLabelAndPercent, AutoText:=True, LegendKey:=False, _
HasLeaderLines:=True
ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.Font.Bold = True
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
ActiveChart.SeriesCollection(1).Points(1).DataLabel.Select
Selection.Left = 166
Selection.Top = 9
ActiveChart.SeriesCollection(1).Points(3).DataLabel.Select
Selection.Left = 430
Selection.Top = 216
ActiveChart.SeriesCollection(1).Points(2).DataLabel.Select
Selection.Left = 541
Selection.Top = 111
ActiveChart.SeriesCollection(1).Points(4).DataLabel.Select
Selection.Left = 24
Selection.Top = 193
ActiveChart.SeriesCollection(1).LeaderLines.Select
ActiveChart.SeriesCollection(1).DataLabels.Select
ActiveChart.SeriesCollection(1).Points(9).DataLabel.Select
Selection.Left = 138
Selection.Top = 256
ActiveChart.SeriesCollection(1).Points(4).DataLabel.Select
Selection.Left = 295
Selection.Top = 254
ActiveChart.SeriesCollection(1).Points(8).DataLabel.Select
Selection.Left = 42
Selection.Top = 152
ActiveChart.SeriesCollection(1).Points(7).DataLabel.Select
Selection.Left = 80
Selection.Top = 99
ActiveChart.SeriesCollection(1).Points(5).DataLabel.Select
Selection.Left = 28
Selection.Top = 110
ActiveChart.SeriesCollection(1).Points(6).DataLabel.Select
Selection.Left = 39
Selection.Top = 62
ActiveChart.SeriesCollection(1).Points(3).DataLabel.Select
Selection.Left = 464
Selection.Top = 213
ActiveChart.SeriesCollection(1).Points(4).DataLabel.Select
Selection.Left = 333
Selection.Top = 260
ActiveChart.SeriesCollection(1).Points(9).DataLabel.Select
Selection.Left = 11
Selection.Top = 213
ActiveChart.SeriesCollection(1).Points(5).DataLabel.Select
Selection.Left = 276
Selection.Top = 260
ActiveChart.SeriesCollection(1).Points(6).DataLabel.Select
Selection.Left = 221
Selection.Top = 254
ActiveChart.SeriesCollection(1).Points(7).DataLabel.Select
Selection.Left = 183
Selection.Top = 255
ActiveChart.SeriesCollection(1).Points(8).DataLabel.Select
Selection.Left = 139
Selection.Top = 252
Range("M1").Select
End Sub


Which works okay, but often times it does not. I was wondering if there was a "full proof way" of ALWAYS having the pie charts facing the same direction, and pie chart tags remain in the same position, so that writing a macro similar to the above one could effectively and successfully be run on my 1k worksheets and save me countless man hours!!

Aflatoon
05-09-2012, 08:42 AM
foolproof way - don't use pie charts generally. you appear to have too much data for a pie chart to be the best way of representing it.

jo15765
05-09-2012, 08:55 AM
I hate pie charts just as much as the next (especially when they are being used for more than 2 or 3 representations) but this is how my client wants the data displayed, so I am forced to use a pie chart to display yit.