Hi all,
in my macro I used the

Chart.Add instruction.

Every thing I want to apply to new chart must be preceded by an Activate call.

How I must do this?
Otherwise I receive always the same Error 1004.

Thanks a lot

A lex.

Alex,

I'm not sure what you are asking, but if you use the macro recorder while creating a chart you get something like the following.

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("C26:C30")
ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet2"

Is this the style of code that is causing your error?

--

John Green - Excel MVP
Sydney
Australia


"y" wrote in message ...
Hi all,
in my macro I used the

Chart.Add instruction.

Every thing I want to apply to new chart must be preceded by an Activate call.

How I must do this?
Otherwise I receive always the same Error 1004.

Thanks a lot

A lex.

Hi John and thank you for your answer,

I posted my question because I used the macro recorder to initialize the macro, but I'm not able to
run it again!

I serched via google "xlSurface group:*Excel*" and it seems to me that Surface2D chart are hard to
manage.

I want an instruction to by-pass the error 1004 that VBA returns to me whan it runs the following
lines of code:

Sub Chart2Dsurface()

Dim sArea As Range
Dim i As Integer
Dim Nseries As Integer

Set sArea = Application.InputBox(prompt:="Select range:", Type:=8)
Nseries = sArea.Rows.Count - 1
Charts.Add.Name = "pippo"

Charts("pippo").ChartType = xlSurface << Error 1004 !!!

Application.WorksheetFunction.Chart.Activate

Thanks Alex.

John Green wrote:

Alex,

I'm not sure what you are asking, but if you use the macro recorder while creating a chart you get something like the following.

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("C26:C30")
ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet2"

Is this the style of code that is causing your error?

Alex,

If your aim is to store a reference to the new chart I suggest creating an object variable.


Set myChart = Charts.Add

With myChart
.Name = "Pippo"
.ChartType = xlSurface
End With

Are you creating a chart sheet or an embedded chart in a worksheet? There are differences in the code required.

--

John Green - Excel MVP
Sydney
Australia


"y" wrote in message ...
Hi John and thank you for your answer,

I posted my question because I used the macro recorder to initialize the macro, but I'm not able to
run it again!

I serched via google "xlSurface group:*Excel*" and it seems to me that Surface2D chart are hard to
manage.

I want an instruction to by-pass the error 1004 that VBA returns to me whan it runs the following
lines of code:

Sub Chart2Dsurface()

Dim sArea As Range
Dim i As Integer
Dim Nseries As Integer

Set sArea = Application.InputBox(prompt:="Select range:", Type:=8)
Nseries = sArea.Rows.Count - 1
Charts.Add.Name = "pippo"

Charts("pippo").ChartType = xlSurface << Error 1004 !!!

Application.WorksheetFunction.Chart.Activate

Thanks Alex.

John Green wrote:

Alex,

I'm not sure what you are asking, but if you use the macro recorder while creating a chart you get something like the following.

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("C26:C30")
ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet2"

Is this the style of code that is causing your error?

Thank you John,
I'll try your suggestions.

First of all I bypassed the problem postponing the the instruction with the xlSurface to the bottom
of my macro. It works.

Second, I would ask you how to convert a range reference to a string within a macro.
The instruction to set the data series needs a string type.

Thank you very much, Alex.

P.S.
I want a chart sheet, but if the alternative is simpler I'll follow the second one.


John Green wrote:
Alex,

If your aim is to store a reference to the new chart I suggest creating an object variable.


Set myChart = Charts.Add

With myChart
.Name = "Pippo"
.ChartType = xlSurface
End With

Are you creating a chart sheet or an embedded chart in a worksheet? There are differences in the code required.

Alex,

You can use the range object directly as in my second reply to your previous post.

Remove the .Location line to create a chart sheet.

--

John Green - Excel MVP
Sydney
Australia


"y" wrote in message ...
Thank you John,
I'll try your suggestions.

First of all I bypassed the problem postponing the the instruction with the xlSurface to the bottom
of my macro. It works.

Second, I would ask you how to convert a range reference to a string within a macro.
The instruction to set the data series needs a string type.

Thank you very much, Alex.

P.S.
I want a chart sheet, but if the alternative is simpler I'll follow the second one.


John Green wrote:
Alex,

If your aim is to store a reference to the new chart I suggest creating an object variable.


Set myChart = Charts.Add

With myChart
.Name = "Pippo"
.ChartType = xlSurface
End With

Are you creating a chart sheet or an embedded chart in a worksheet? There are differences in the code required.

The following code works for me under Excel 2003

Sub Chart2Dsurface()

Dim sArea As Range
Dim i As Integer
Dim Nseries As Integer
Dim myChart As Chart

Set sArea = Application.InputBox(prompt:="Select range:", Type:=8)
Nseries = sArea.Rows.Count - 1

Set myChart = Charts.Add
With myChart
.SetSourceData Source:=sArea
.Name = "Pippo"
.ChartType = xlSurface
.Location whe=xlLocationAsObject, Name:="Sheet2"
End With

End Sub


--

John Green - Excel MVP
Sydney
Australia


"y" wrote in message ...
Hi John and thank you for your answer,

I posted my question because I used the macro recorder to initialize the macro, but I'm not able to
run it again!

I serched via google "xlSurface group:*Excel*" and it seems to me that Surface2D chart are hard to
manage.

I want an instruction to by-pass the error 1004 that VBA returns to me whan it runs the following
lines of code:

Sub Chart2Dsurface()

Dim sArea As Range
Dim i As Integer
Dim Nseries As Integer

Set sArea = Application.InputBox(prompt:="Select range:", Type:=8)
Nseries = sArea.Rows.Count - 1
Charts.Add.Name = "pippo"

Charts("pippo").ChartType = xlSurface << Error 1004 !!!

Application.WorksheetFunction.Chart.Activate

Thanks Alex.

John Green wrote:

Alex,

I'm not sure what you are asking, but if you use the macro recorder while creating a chart you get something like the following.

Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("Sheet2").Range("C26:C30")
ActiveChart.Location Whe=xlLocationAsObject, Name:="Sheet2"

Is this the style of code that is causing your error?

Great!

Anyway as you noted my area has both data and label. I'm not experienced of object and how treat
them. Where can I get references about charts and their properties?

Shortly, I want to manage the X axis values and and labels (my Y axis values). How may I do that?
How to use the information get from user ( Inputbox(prom.... ) to set Xvalues and the labels?

If I want to manage other features of a graph?

Thanks Alex.

John Green wrote:
The following code works for me under Excel 2003

Sub Chart2Dsurface()

Dim sArea As Range
Dim i As Integer
Dim Nseries As Integer
Dim myChart As Chart

Set sArea = Application.InputBox(prompt:="Select range:", Type:=8)
Nseries = sArea.Rows.Count - 1

Set myChart = Charts.Add
With myChart
.SetSourceData Source:=sArea
.Name = "Pippo"
.ChartType = xlSurface
.Location whe=xlLocationAsObject, Name:="Sheet2"
End With

End Sub