Excel 2000 Chart & VBA: why oh why?!?
 

Hi everyone. I created a macro with the Record New Macro in Excel to
create a chart "Lines on 2 Axes". When I create the chart by hand, all
goes well, but when I replay the macro it crashes with the error
"Run-time error '1004' Method'Axes' of object'_Chart' failed". Can
anyone help me here? I am completely desperate, I don't know what to
do. I tried Jon Peltier's answer to this
(http://groups.google.com/groups?q=Me...ftngp05&rnum=6)
question but it doesn't seem to work for me.

Here's my code:

Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn,
TypeName:="Line - Column on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets("Tempo Médio de
Espera").Range("A1")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
'ActiveChart.SeriesCollection(1).AxisGroup = 3
ActiveChart.SeriesCollection(1).XValues = "='Tempo Médio de
Espera'!R4C2:R15C2"
ActiveChart.SeriesCollection(1).Values = "='Tempo Médio de
Espera'!R4C4:R15C4"
ActiveChart.SeriesCollection(1).Name = "='Tempo Médio de
Espera'!R3C4"
ActiveChart.SeriesCollection(2).Values = "='Tempo Médio de
Espera'!R4C6:R15C6"
ActiveChart.SeriesCollection(2).Name = "='Tempo Médio de
Espera'!R3C6"
ActiveChart.SeriesCollection(3).Values = "='Tempo Médio de
Espera'!R4C8:R15C8"
ActiveChart.SeriesCollection(3).Name = "='Tempo Médio de
Espera'!R3C8"
ActiveChart.SeriesCollection(4).Values = "='Tempo Médio de
Espera'!R4C3:R15C3"
ActiveChart.SeriesCollection(4).Name = "='Tempo Médio de
Espera'!R3C3"
ActiveChart.SeriesCollection(5).Values = "='Tempo Médio de
Espera'!R4C5:R15C5"
ActiveChart.SeriesCollection(5).Name = "='Tempo Médio de
Espera'!R3C5"
ActiveChart.SeriesCollection(6).Values = "='Tempo Médio de
Espera'!R4C7:R15C7"
ActiveChart.SeriesCollection(6).Name = "='Tempo Médio de
Espera'!R3C7"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Tempo
Médio de Espera"

With ActiveChart
'.SeriesCollection(6).AxisGroup = 2
.HasTitle = True
.ChartTitle.Characters.Text = "Evolução Horária Tempo
Médio de Espera"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Espaço Tempo Horário"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Nº
Atendimentos"
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"Tempo Médio Horário"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.HasDataTable = False

Excel 2000 Chart & VBA: why oh why?!?
 

Hi,

I can hardly remember, but it could be possible that you have to "select" or
"actiave" the object you would like to change with your VBA code. I think
you can try this if you use the debugging modus. So, you get the error, you
chose debugging, then you go to that chart and click on the axes that should
be edited, go back to debugging and start again the paused makro from where
it stopped. When the makro then works then you need add a line to your code
that will select the axis or whatever.

Have a try

arno

"Run-time error '1004' Method'Axes' of object'_Chart' failed". Can

Excel 2000 Chart & VBA: why oh why?!?
 

Hi Rui -

You shouldn't have to select an element to edit it.

You didn't indicate which line threw the error.

I would not have assigned the built in chart type. I would have started
with a column chart, added each series and defined its X and Y range and
name, then if appropriate changed its chart type and the axis it is
assigned to.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Rui Pacheco wrote:

Hi everyone. I created a macro with the Record New Macro in Excel to
create a chart "Lines on 2 Axes". When I create the chart by hand, all
goes well, but when I replay the macro it crashes with the error
"Run-time error '1004' Method'Axes' of object'_Chart' failed". Can
anyone help me here? I am completely desperate, I don't know what to
do. I tried Jon Peltier's answer to this
(http://groups.google.com/groups?q=Me...ftngp05&rnum=6)
question but it doesn't seem to work for me.

Here's my code:

Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn,
TypeName:="Line - Column on 2 Axes"
ActiveChart.SetSourceData Source:=Sheets("Tempo Médio de
Espera").Range("A1")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
'ActiveChart.SeriesCollection(1).AxisGroup = 3
ActiveChart.SeriesCollection(1).XValues = "='Tempo Médio de
Espera'!R4C2:R15C2"
ActiveChart.SeriesCollection(1).Values = "='Tempo Médio de
Espera'!R4C4:R15C4"
ActiveChart.SeriesCollection(1).Name = "='Tempo Médio de
Espera'!R3C4"
ActiveChart.SeriesCollection(2).Values = "='Tempo Médio de
Espera'!R4C6:R15C6"
ActiveChart.SeriesCollection(2).Name = "='Tempo Médio de
Espera'!R3C6"
ActiveChart.SeriesCollection(3).Values = "='Tempo Médio de
Espera'!R4C8:R15C8"
ActiveChart.SeriesCollection(3).Name = "='Tempo Médio de
Espera'!R3C8"
ActiveChart.SeriesCollection(4).Values = "='Tempo Médio de
Espera'!R4C3:R15C3"
ActiveChart.SeriesCollection(4).Name = "='Tempo Médio de
Espera'!R3C3"
ActiveChart.SeriesCollection(5).Values = "='Tempo Médio de
Espera'!R4C5:R15C5"
ActiveChart.SeriesCollection(5).Name = "='Tempo Médio de
Espera'!R3C5"
ActiveChart.SeriesCollection(6).Values = "='Tempo Médio de
Espera'!R4C7:R15C7"
ActiveChart.SeriesCollection(6).Name = "='Tempo Médio de
Espera'!R3C7"
ActiveChart.Location Whe=xlLocationAsObject, Name:="Tempo
Médio de Espera"

With ActiveChart
'.SeriesCollection(6).AxisGroup = 2
.HasTitle = True
.ChartTitle.Characters.Text = "Evolução Horária Tempo
Médio de Espera"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text =
"Espaço Tempo Horário"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Nº
Atendimentos"
.Axes(xlCategory, xlSecondary).HasTitle = False
.Axes(xlValue, xlSecondary).HasTitle = True
.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text =
"Tempo Médio Horário"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = True
ActiveChart.Legend.Select
Selection.Position = xlBottom
ActiveChart.HasDataTable = False