Excel VBA: chart numbering

[Marco Shaw]

Outlook 2003 (using macro recorder and chart wizard)

What I'm trying to do:

I have some simple VBA to create a pie chart:

Sub pie_chart()
'
' pie_chart Macro
'

'
Range("E1").Select
ActiveCell.FormulaR1C1 = "=RC[-3]"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=R[10]C[-3]"
Range("E3").Select
ActiveCell.FormulaR1C1 = "=R[20]C[-3]"
Range("E4").Select
ActiveCell.FormulaR1C1 = "=R[30]C[-3]"
Range("E5").Select
ActiveCell.FormulaR1C1 = "=R[40]C[-3]"
Range("E6").Select
ActiveCell.FormulaR1C1 = "=R[50]C[-3]"
Range("F1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:R[10]C[-3])"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=SUM(R[10]C[-3]:R[20]C[-3])"
Range("F3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[20]C[-3]:R[30]C[-3])"
Range("F4").Select
ActiveCell.FormulaR1C1 = "=SUM(R[30]C[-3]:R[40]C[-3])"
Range("F5").Select
ActiveCell.FormulaR1C1 = "=SUM(R[40]C[-3]:R[50]C[-3])"
Range("F6").Select
ActiveCell.FormulaR1C1 = "=SUM(R[50]C[-3]:R[60]C[-3])"
Range("E1:F6").Select
Charts.Add
ActiveChart.ChartType = xl3DPieExploded
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("E1:F6"),
PlotBy:= _
xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
Application.CommandBars("Chart").Visible = False
End Sub

Then another simple sub, since I want to break certain things down into
pieces, where this code will simply change the pie chart formatting:

Sub pie_chart2()
'
' pie_chart2 Macro
'

'
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.ApplyDataLabels AutoText:=True, LegendKey:=False, _
HasLeaderLines:=True, ShowSeriesName:=False,
ShowCategoryName:=False, _
ShowValue:=False, ShowPercentage:=True, ShowBubbleSize:=False
ActiveChart.PlotArea.Select
Selection.ClearFormats
End Sub

My problem:

I've googled around, played a bit with .Name, but in the 2nd sub, I can't
figure out how to 'activate' the chart created from the 1st sub properly.

I'll have a couple of charts like this on the same sheet, so I should
probably refer to them by name.

Error:
Run-time error '1004':
Unable to get the ChartObjects property of the Worksheet class

Any ideas?

Marco

 

[Eric Legault [MVP - Outlook]]

Wrong group Marco! Try the Excel programming group.